PowerShell script to stop and Start SQL Server Services

As most of you know PowerShell is an amazing tool to perform a lot of Database Administration tasks.
Since last year I have been trying to create PS scripts to make my life easy. One of the tasks that I perform during our annual DR testing is stopping and starting SQL Server Services.
Before you had to login to the DB server, open the SQL Server configuration manager, change the Service Startup method (AUTO or MANUAL) and then stop/start the service. If you had SSRS, SSAS, SSIS or multiple instances then this could take time. Not to forget only the DBAs would do this.

Just like all other DBAs I am pretty lazy. That is why I wanted all my tasks automated so I can work on new features and upgrade my environment. So I wrote this script to Start SQL Server Services. You will have to modify this if you want a script to STOP the services. Also, edit the Get-Service command to display the servicenames in Descending order if you are stopping the services. This is to ensure that you stop the Agent before the engine.

Note: I found a script to change the account that runs the SQL Server services from the following link. I have modifed that script to create this one.
http://blogs.msdn.com/b/amantaras/archive/2014/12/10/powershell-script-to-change-windows-service-credentials.aspx



#This script requires the hostname as the input.
[CmdletBinding()]
Param( [Parameter(Mandatory=$True,Position=1)][string]$Server )

function PowerShell-Wait($seconds)
{
#This function will cause the script to wait n seconds
   [System.Threading.Thread]::Sleep($seconds*4000)
}

$host.PrivateData.VerboseBackgroundColor = "DarkMagenta"

$host.PrivateData.WarningBackgroundColor = "DarkMagenta"

$services=Get-Service -ComputerName $Server -Displayname "SQL*" | Sort-Object -Property Name
write-host "----------------------------------------------------------------"  
write-host "REMEMBER TO RUN THE SCRIPT AS ADMINISTRATOR"  -foregroundcolor "RED" -backgroundcolor "yellow"

write-host "`n Status of SQL Services" -foregroundcolor "green"
Get-Service -ComputerName $Server -Displayname "SQL*" | Sort-Object -Property Name
write-host "`n Services found:"  $services.Count 
Foreach ($servicename in $services)

{
if ($servicename.Name -eq "SQLWriter" -or $servicename.Displayname -eq "SQL Active Directory Helper Service") 
{ 
write-host "Skipping service : " $servicename.Displayname -foregroundcolor "RED"
Continue 
}


$startmode = gwmi win32_service -computername $Server | where {$_.Displayname -like $servicename.Displayname} | select StartMode
if ( $startmode.startmode -eq "Disabled")
{
write-host "Service "$servicename.Displayname " is DISABLED so skipping service." -foregroundcolor "RED"
Continue
}


Write-Host "Do you want to Start the service "$servicename.Displayname" on "$Server -foregroundcolor "Yellow" -backgroundcolor "Blue"
$choice = Read-Host -Prompt '[Y/N] :'
if ($choice -eq "Y" -or $choice -eq "YES" -or $choice -eq "Yes" -or $choice -eq "yes" ) 
{
write-host "Starting service: "   $servicename.name -foregroundcolor "green"
write-host "    Attempting to Start de service..." -foregroundcolor "green"
Set-Service -InputObject $servicename -startuptype "Automatic" -Verbose 
Start-Service -InputObject $servicename -Verbose 
PowerShell-Wait (1)
}
Else
{
write-host "Skipping service : " $servicename.Displayname -foregroundcolor "RED"
}
}


write-host "`n Status of SQL Services" -foregroundcolor "green"
Get-Service -ComputerName $Server -Displayname "SQL*" | Sort-Object -Property Name | Format-Table -Property Status, Name , DisplayName -auto  | Out-String

write-host "PROCESS COMPLETED"  -foregroundcolor "RED" -backgroundcolor "yellow"