Wednesday, November 2, 2016

Powershell script to re-start SQL Server services for multiple servers

Step1: Create folder D:\Test\
Step2:Open notepad and add all servers one after one as per below in "D:\Test\ServersList.txt" file
Server1
Server2
Server3
...etc

Step2: Create Test.ps1 powershell script file with below code in "D:\Test" folder
D:\Test\Test.ps1 file code:

Get-Content D:\Test\ServersList.txt | % {

 $Server=$_

 Write-Host  "Server Name " : $Server

If(Test-Connection $Server -Quiet)
{

 $Srv=Get-Service -Name MSSQLSERVER -ComputerName $Server

  If ($Srv.Status -eq "Running")
   {
Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Stop-Service -Verbose -Force
Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Select name, status

Get-Service -ComputerName $Server -Name MSSQLSERVER | Stop-Service -Verbose -Force
Get-Service -ComputerName $Server -Name MSSQLSERVER | Select name, status

Get-Service -ComputerName $Server -Name MSSQLSERVER | Restart-Service -Verbose -Force
Get-Service -ComputerName $Server -Name MSSQLSERVER | Select name, status

Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Restart-Service -Verbose -Force
Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Select name, status

    } Else {
      Write-Host  "SQL Server Service not running"
    }
 } else {
      Write-Host  "Failed to connec to servers:" $Server
  }| Export-Csv D:\Test\FailedConnLog.txt -NoTypeInformation
} | Export-Csv D:\Test\OutputLog.txt -NoTypeInformation


OR
$SList= Get-Content "D:\Test\ServersList.txt"
ForEach ($Server in  $SList)
{
  Write-Host $Server
#### Write code here ######
}

Command to execute or schedule in job:
D:\\powershell.exe -Noninteractive -Inputformat none -Executionpolicy Bypass -Command D:\Test\Test.ps1