Powershell Scripts
Disable or Drop Sql Server Agent Jobs |
<#####################################
this script will disable a list of jobs on a sql server and will print each job that is disabled after completed. set location to the jobs directory on the sql server cd sqlserver:\sql\<YOUR SERVER NAME>\default\jobserver\jobs use the where-object "?" to filter the list of jobs. #####################################> $jobs = dir | ?{$_.name -match "<JOB TO DISABLE>"} $jobs | %{$_.IsEnabled = $false; $_.alter(); write-host $_.name " is disabled"} <##################################### code below will delete the job dir | ?{$_.name -match "<JOB TO DELETE>"} | %{$_.drop();$_.name} #####################################> |
Kill a Process |
|
Today I needed to kill a process, while installing a Service Pack on a 2012 Instance.
Use the cmdlet Get-Process to find the Process Name and Process ID You can filter the list of processes with the where-object. Here is an example |
Get-Process | where-object{$_.processname -eq "<process name>"}
After finding the ID then execute the following Stop-Process <Process ID#> or you can do this Stop-Process -processorname "<process name>" |
How to Zip Up files with Powershell |
|
A customer needed me to create zip files. Here is the code to do that. You will need to download the PCSX module. All the info needed can be found in the comments of the script. Pretty awesome stuff.
https://pscx.codeplex.com/ |
<#
In order to run this script you must have the PCSX module installed. Download from the link below https://pscx.codeplex.com/ #> cls $A = Get-ChildItem C:\MYStuff -Filter "*.txt" <# set location of where the files are you want to zip up set level of compression 1-9 variable provides files to be zipped output tells where to put the zip file #> cd C:\MYStuff Write-Zip -Input $a -level 3 -Output C:\Mystuff\TextZip3.zip <# ##Remove items you just zipped up remove-item $a #> |
Pipe Invoke-SQLCMD to CSV file |
|
If you need to create a clean CSV file to be imported into EXCEL here is a good piece of code. Found it on the Beyond Relational website
Invoke-SqlCmd -ServerInstance <Servername> -Database <DBName> -Query "SELECT * FROM Table" |
ConvertTo-Csv -NoType |
ForEach-Object {$_.Replace('"','')}|
Out-file c:\test.csv
Invoke-SqlCmd -ServerInstance <Servername> -Database <DBName> -Query "SELECT * FROM Table" |
ConvertTo-Csv -NoType |
ForEach-Object {$_.Replace('"','')}|
Out-file c:\test.csv
Find number of cores and logical processors |
|
Trying to find number of cores and logical processes with SQL kinda sucks and leaves room for error. So I thought Powershell would be a better method..
Get-WmiObject win32_processor -ComputerName <servername> -Property Numberofcores, socketdesignation, numberoflogicalprocessors, description | ft socketdesignation, description, numberofcores, numberoflogicalprocessors -auto
Get-WmiObject win32_processor -ComputerName <servername> -Property Numberofcores, socketdesignation, numberoflogicalprocessors, description | ft socketdesignation, description, numberofcores, numberoflogicalprocessors -auto
Find accounts SQL Server is running under across your environment |
|
Compliance department want to know the name of the SQL Server Services and what accounts they were running under across the environment.
Here is what i came up with..
cls
cd c:\ps
import-module sqlps -disablenamechecking
##initialize variable
$Instance = $()
##list of serverrs to iterate through. this could be a tex file of servers, example get-content c:\listofservers.txt
$Instance = invoke-sqlcmd -ServerInstance <SQL Server Envirorment Repository> -Database <Repo DB> -query "<Select Servername from Server Repository>" -SuppressProviderContextWarning
##counter
$i =1
$count = $Instance.count
##header for text file. guess i could have done export-csv but anyway this works
"Server"+"|"+"SQL Service"+"|"+"SQL Service Account"+"|"+"SQL Agent"+"|"+"SQL Agent Account" | out-file C:\<filepath\name of file.txt>
foreach($Server in $Instance){
##make sure you have a object that can be used as a string and not a datarow object
$computer = $($Server.Servername)
## find sql server services and service accounts
$Service = Get-WmiObject win32_service -ComputerName $computer | where {$_.Name -eq 'MSSQLServer' -or $_.Name -eq 'SQLSERVERAGENT' }
##write counter to screen(I hate looking at a blank screen)
write-host $i"..of.."$count
##create text file and append each iteration to that file
$computer +"|"+$($Service[0].name) +"|" +$($Service[0].startname) +"|"+$($Service[1].name)+ "|"+$($Service[1].startname ) | out-file C:\<filepath\name of file.txt> -Append
$i++
}
Here is what i came up with..
cls
cd c:\ps
import-module sqlps -disablenamechecking
##initialize variable
$Instance = $()
##list of serverrs to iterate through. this could be a tex file of servers, example get-content c:\listofservers.txt
$Instance = invoke-sqlcmd -ServerInstance <SQL Server Envirorment Repository> -Database <Repo DB> -query "<Select Servername from Server Repository>" -SuppressProviderContextWarning
##counter
$i =1
$count = $Instance.count
##header for text file. guess i could have done export-csv but anyway this works
"Server"+"|"+"SQL Service"+"|"+"SQL Service Account"+"|"+"SQL Agent"+"|"+"SQL Agent Account" | out-file C:\<filepath\name of file.txt>
foreach($Server in $Instance){
##make sure you have a object that can be used as a string and not a datarow object
$computer = $($Server.Servername)
## find sql server services and service accounts
$Service = Get-WmiObject win32_service -ComputerName $computer | where {$_.Name -eq 'MSSQLServer' -or $_.Name -eq 'SQLSERVERAGENT' }
##write counter to screen(I hate looking at a blank screen)
write-host $i"..of.."$count
##create text file and append each iteration to that file
$computer +"|"+$($Service[0].name) +"|" +$($Service[0].startname) +"|"+$($Service[1].name)+ "|"+$($Service[1].startname ) | out-file C:\<filepath\name of file.txt> -Append
$i++
}
See if you can connect to list of Servers |
|
,At my current employer we have some lets just say inconsistencies across the environment regarding Server builds and GPO and firewall settings. I am sure, no one has ever dealt with this before!! So, I have a ton of boxes I can't connect to with Powershell and I need to know why. This script does that via the Test-Connection cmdlet and then generates a .txt file of with either connection success or connection failure and the error. I will then take this to my server team to fix the issues.
You will need to enter in a few things. File paths for the .txt files it generates and a location to a list of servers or connection info to SQL Server table where you store your server info.
cls
cd c:\ps
import-module sqlps -disablenamechecking
##initialize variable
$Instance = $()
##list of serverrs to iterate through. this could be a tex file of servers get-content <filepath to list of servers in a txt file>
$Instance = invoke-sqlcmd -ServerInstance <server name> -Database ServerRepo -query "select Servername from dbo.servers " -SuppressProviderContextWarning
##counter
$i =1
$count = $Instance.count
foreach($Server in $Instance){
##make sure you have a object that can be used as a string and not a datarow object
$computer = $()
$computer = $($Server.Servername)
try{
$a = $()
$a = Test-Connection -ComputerName $computer -BufferSize 16 -Count 1 -erroraction "stop"
##write counter to screen
write-host $i"..of.."$count
write-host "can connect to " $computer
$computer | out-file C:\filepath\Connection_Successful_TestConnection.txt -append
}
catch [System.Runtime.InteropServices.COMException]
{
#Attempt to query the server via WMI Failed
Write-Host WMI FAILURE! -ForegroundColor green -BackgroundColor Yellow
$computer+"|"+$Error[0].Exception.GetType().FullName | out-file C:\filepath\Connection_NOT_Successful_WMI_Error_TestConnection.txt -append
}
catch [Exception]
{
#General Failure
Write-Host GENERAL FAILURE! for $computer -ForegroundColor Red
<#When I want to know the full exception type:#>
Write-Host $Error[0].Exception.GetType().FullName -BackgroundColor blue -ForegroundColor Yellow
$computer+"|"+$Error[0].Exception.GetType().FullName | out-file C:\filepath\Connection_NOTSuccessful_TestConnection.txt -append
}
$i++
}
Here is a second script using the test-path cmdlet. It will return a different error than test-connection if there is a connection issue.
cls
cd c:\ps
import-module sqlps -disablenamechecking
##initialize variable
$Instance = $()
##list of serverrs to iterate through. this could be a tex file of servers get-content <filepath to list of servers in a txt file>
$Instance = invoke-sqlcmd -ServerInstance <servername>-Database <server repo>-query "select Servername from dbo.servers " -SuppressProviderContextWarning
##counter
$i =1
$count = $Instance.count
foreach($Server in $Instance){
##make sure you have a object that can be used as a string and not a datarow object
$computer = $()
$computer = $($Server.Servername)
try{
$a = $()
##$a = Test-Connection -ComputerName $computer -BufferSize 16 -Count 1 -erroraction "stop"
$a = Test-Path -path \\$computer\C$\Windows -erroraction "stop"
##write counter to screen
write-host $i"..of.."$count
write-host "can connect to " $computer
$computer | out-file C:\Users\mdspain\Desktop\Connection_Successful_TestPath.txt -append
}
catch [System.Runtime.InteropServices.COMException]
{
#Attempt to query the server via WMI Failed
Write-Host WMI FAILURE! -ForegroundColor green -BackgroundColor Yellow
$computer+".."+$Error[0].Exception.GetType().FullName | out-file C:\Users\mdspain\Desktop\Connection_WMIError_TestPath.txt -append
}
catch [Exception]
{
#General Failure
Write-Host GENERAL FAILURE! for $computer -ForegroundColor Red
<#When I want to know the full exception type:#>
Write-Host $Error[0].Exception.GetType().FullName -BackgroundColor blue -ForegroundColor Yellow
$computer+".."+$Error[0].Exception.GetType().FullName | out-file C:\Users\mdspain\Desktop\Connection_Error_TestPath.txt -append
}
$i++
}
You will need to enter in a few things. File paths for the .txt files it generates and a location to a list of servers or connection info to SQL Server table where you store your server info.
cls
cd c:\ps
import-module sqlps -disablenamechecking
##initialize variable
$Instance = $()
##list of serverrs to iterate through. this could be a tex file of servers get-content <filepath to list of servers in a txt file>
$Instance = invoke-sqlcmd -ServerInstance <server name> -Database ServerRepo -query "select Servername from dbo.servers " -SuppressProviderContextWarning
##counter
$i =1
$count = $Instance.count
foreach($Server in $Instance){
##make sure you have a object that can be used as a string and not a datarow object
$computer = $()
$computer = $($Server.Servername)
try{
$a = $()
$a = Test-Connection -ComputerName $computer -BufferSize 16 -Count 1 -erroraction "stop"
##write counter to screen
write-host $i"..of.."$count
write-host "can connect to " $computer
$computer | out-file C:\filepath\Connection_Successful_TestConnection.txt -append
}
catch [System.Runtime.InteropServices.COMException]
{
#Attempt to query the server via WMI Failed
Write-Host WMI FAILURE! -ForegroundColor green -BackgroundColor Yellow
$computer+"|"+$Error[0].Exception.GetType().FullName | out-file C:\filepath\Connection_NOT_Successful_WMI_Error_TestConnection.txt -append
}
catch [Exception]
{
#General Failure
Write-Host GENERAL FAILURE! for $computer -ForegroundColor Red
<#When I want to know the full exception type:#>
Write-Host $Error[0].Exception.GetType().FullName -BackgroundColor blue -ForegroundColor Yellow
$computer+"|"+$Error[0].Exception.GetType().FullName | out-file C:\filepath\Connection_NOTSuccessful_TestConnection.txt -append
}
$i++
}
Here is a second script using the test-path cmdlet. It will return a different error than test-connection if there is a connection issue.
cls
cd c:\ps
import-module sqlps -disablenamechecking
##initialize variable
$Instance = $()
##list of serverrs to iterate through. this could be a tex file of servers get-content <filepath to list of servers in a txt file>
$Instance = invoke-sqlcmd -ServerInstance <servername>-Database <server repo>-query "select Servername from dbo.servers " -SuppressProviderContextWarning
##counter
$i =1
$count = $Instance.count
foreach($Server in $Instance){
##make sure you have a object that can be used as a string and not a datarow object
$computer = $()
$computer = $($Server.Servername)
try{
$a = $()
##$a = Test-Connection -ComputerName $computer -BufferSize 16 -Count 1 -erroraction "stop"
$a = Test-Path -path \\$computer\C$\Windows -erroraction "stop"
##write counter to screen
write-host $i"..of.."$count
write-host "can connect to " $computer
$computer | out-file C:\Users\mdspain\Desktop\Connection_Successful_TestPath.txt -append
}
catch [System.Runtime.InteropServices.COMException]
{
#Attempt to query the server via WMI Failed
Write-Host WMI FAILURE! -ForegroundColor green -BackgroundColor Yellow
$computer+".."+$Error[0].Exception.GetType().FullName | out-file C:\Users\mdspain\Desktop\Connection_WMIError_TestPath.txt -append
}
catch [Exception]
{
#General Failure
Write-Host GENERAL FAILURE! for $computer -ForegroundColor Red
<#When I want to know the full exception type:#>
Write-Host $Error[0].Exception.GetType().FullName -BackgroundColor blue -ForegroundColor Yellow
$computer+".."+$Error[0].Exception.GetType().FullName | out-file C:\Users\mdspain\Desktop\Connection_Error_TestPath.txt -append
}
$i++
}
Change Sql Server Service Accounts and Passwords |
|
I need to change the SQL Server Service Accounts across the enterprise. Here is what I came up with. I initially had a rough draft that was basic but then found this script at
https://gallery.technet.microsoft.com/scriptcenter/79644be9-b5e1-4d9e-9cb5-eab1ad866eaf
I added some fixes for issues I found during testing.
cls
####################################
##
## change SQL Server Service Account and Password
##
## if doing this for multiple server create a text file with the list of servers
## additional functionality would need to be added if you want to also pass account name and password as variables but would be easy to add
## output will be written to screen reporting success or failure
##
####################################
##server list from text file
$serverlist = Get-Content C:\Users\mdspain\Desktop\desktop_server_test.txt
foreach($server in $serverlist)
{
##get sql service
$service = get-wmiObject win32_service -computername $server | ?{$_.name -eq "mssqlserver"}
# change sql service account and password
$changestatus = $service.change($null,$null,$null,$null,$null,$null,"<user name>","<enter password>",$null, $null, $null)
## start-sleep added due to failures during testing. this seemed to resolve the failures
start-sleep -s 2
if($changestatus.returnvalue -eq 0)
{
write-host $server'......service change successfull' -BackgroundColor Blue -ForegroundColor White
}
if($changestatus.returnvalue -gt 0) ##if fails see what the return value is. this link describes the return values https://msdn.microsoft.com/en-us/library/windows/desktop/aa393660(v=vs.85).aspx
{
write-host $server'......service change successfull' -BackgroundColor red -ForegroundColor yellow
}
## stop service after service account change
$stopservice = $service.stopservice()
## start-sleep added due to failures during testing. this seemed to resolve the failures
Start-Sleep -s 2
if($stopservice.returnvalue -eq 0) ##if fails see what the return value is. this link describes the return values https://msdn.microsoft.com/en-us/library/windows/desktop/aa393660(v=vs.85).aspx
{
write-host $server'.....stopped successfully' -BackgroundColor Blue -ForegroundColor White
}
if($stopservice.returnvalue -gt 0)
{
write-host $server'....stopped unsuccessfully' -BackgroundColor red -ForegroundColor yellow
}
## start service account and account change will take effect
$startservice = $service.startservice()
if($startservice.returnvalue -eq 0)
{
write-host $server'.....started successfully' -BackgroundColor Blue -ForegroundColor White
}
if($startservice.returnvalue -gt 0) ##if fails see what the return value is. this link describes the return values https://msdn.microsoft.com/en-us/library/windows/desktop/aa393660(v=vs.85).aspx
{
write-host $server'.....started unsuccessfully' -BackgroundColor red -ForegroundColor yellow
}
}
https://gallery.technet.microsoft.com/scriptcenter/79644be9-b5e1-4d9e-9cb5-eab1ad866eaf
I added some fixes for issues I found during testing.
cls
####################################
##
## change SQL Server Service Account and Password
##
## if doing this for multiple server create a text file with the list of servers
## additional functionality would need to be added if you want to also pass account name and password as variables but would be easy to add
## output will be written to screen reporting success or failure
##
####################################
##server list from text file
$serverlist = Get-Content C:\Users\mdspain\Desktop\desktop_server_test.txt
foreach($server in $serverlist)
{
##get sql service
$service = get-wmiObject win32_service -computername $server | ?{$_.name -eq "mssqlserver"}
# change sql service account and password
$changestatus = $service.change($null,$null,$null,$null,$null,$null,"<user name>","<enter password>",$null, $null, $null)
## start-sleep added due to failures during testing. this seemed to resolve the failures
start-sleep -s 2
if($changestatus.returnvalue -eq 0)
{
write-host $server'......service change successfull' -BackgroundColor Blue -ForegroundColor White
}
if($changestatus.returnvalue -gt 0) ##if fails see what the return value is. this link describes the return values https://msdn.microsoft.com/en-us/library/windows/desktop/aa393660(v=vs.85).aspx
{
write-host $server'......service change successfull' -BackgroundColor red -ForegroundColor yellow
}
## stop service after service account change
$stopservice = $service.stopservice()
## start-sleep added due to failures during testing. this seemed to resolve the failures
Start-Sleep -s 2
if($stopservice.returnvalue -eq 0) ##if fails see what the return value is. this link describes the return values https://msdn.microsoft.com/en-us/library/windows/desktop/aa393660(v=vs.85).aspx
{
write-host $server'.....stopped successfully' -BackgroundColor Blue -ForegroundColor White
}
if($stopservice.returnvalue -gt 0)
{
write-host $server'....stopped unsuccessfully' -BackgroundColor red -ForegroundColor yellow
}
## start service account and account change will take effect
$startservice = $service.startservice()
if($startservice.returnvalue -eq 0)
{
write-host $server'.....started successfully' -BackgroundColor Blue -ForegroundColor White
}
if($startservice.returnvalue -gt 0) ##if fails see what the return value is. this link describes the return values https://msdn.microsoft.com/en-us/library/windows/desktop/aa393660(v=vs.85).aspx
{
write-host $server'.....started unsuccessfully' -BackgroundColor red -ForegroundColor yellow
}
}
Find all AD Groups a User is in |
|
Need to find all groups a user is a part of... here you go
Get-ADPrincipalGroupMembership <user name > | select name
Get-ADPrincipalGroupMembership <user name > | select name
Find if RDP ports are open |
|
We have a lot of connection challenges due to security and compliance standards put in place at my current employer. Need to find which RDP port aren't open.
Populate $Instance with a list of severs. I am doing that with my repository database.
$Instance = invoke-sqlcmd -ServerInstance <servername> -Database RepositoryofServers -query "select Servername from dbo.servers where SQl_Server_Build_Version is not null" -SuppressProviderContextWarning
Foreach($server in $Instance.servername){
$server
$test = Test-NetConnection -ComputerName $server -CommonTCPPort RDP
$test
$server +"|"+$test.pingsucceeded | out-file C:\path to file\RDP_Test.txt -Append
}
Populate $Instance with a list of severs. I am doing that with my repository database.
$Instance = invoke-sqlcmd -ServerInstance <servername> -Database RepositoryofServers -query "select Servername from dbo.servers where SQl_Server_Build_Version is not null" -SuppressProviderContextWarning
Foreach($server in $Instance.servername){
$server
$test = Test-NetConnection -ComputerName $server -CommonTCPPort RDP
$test
$server +"|"+$test.pingsucceeded | out-file C:\path to file\RDP_Test.txt -Append
}
Weird Error when using SQLPS and MERGE statement with on OUTPUT Clause in Powershell. |
|
So I have a script that takes data from a source and puts it into a staging table then using the MERGE statement updates the production table. I used the OUTPUT clause to return data to the screen just to verify how the MERGE was working. The inserted fields and deleted fields from the OUTPUT clause had the same name. Works fine in SQL but SQLPS bombed. It didn't like the two columns with the same name. I missed it in my testing.