This is one of those extremely simple posts where everyone who does PowerShell knows how ridiculously simple it is to do and you will too today. You will look like a magician to the uninitiated.
Today, I had about 5 hosts running 4 instances of SQL Server each and I had to
- Stop all SQL Server related services
- Change the StartupType of each SQL related service to “Disabled”
This is in preparation for decommissioning the servers after a period of time in this state.
If you are thinking about RDP’ing into the servers, you are certainly thinking it wrong. With PowerShell, we are taking about saving hundred or more mouse clicks, at least.
Listing SQL Server Services:
To list all the Windows Services related to SQL Server, simply issue
Get-Service *sql*
This should give you back something like below:
PS C:\Users\MyWinID> get-service *sql* Status Name DisplayName ------ ---- ----------- Running MSSQL$Inst01 SQL Server (Inst01) Stopped MSSQL$Inst02 SQL Server (Inst02) Stopped MSSQL$Inst03 SQL Server (Inst03) Stopped MSSQL$Inst04 SQL Server (Inst04) Running SQLAgent$Inst01 SQL Server Agent (Inst01) Stopped SQLAgent$Inst02 SQL Server Agent (Inst02) Stopped SQLAgent$Inst03 SQL Server Agent (Inst03) Stopped SQLAgent$Inst04 SQL Server Agent (Inst04) Running SQLBrowser SQL Server Browser Running SQLWriter SQL Server VSS Writer
If you are doing this from another host, just include the ComputerName
Get-Service -ComputerName MySQLServerHost *sql*
Stopping SQL Server Services:
All the above services are the ones I need to stop. To do so, you simply pipe all the SQL Services to “Stop-Service” commandlet!
Get-Service *sql* | Stop-Service -Force
Run this a few times if there dependent services that are not getting stopped. After a few attempts, all SQL Server services should be stopped. Do you see how simple it is?!
Again, include the “ComputerName” parameter if you are doing it from your own machine and the SQL Server host is different.
Verify that all the services are stopped:
Get-Service *sql*
Change the StartupType of SQL Server Services:
After stopping all the services, I want to set the “StartupType” of the SQL Services to “Disabled” (you could set it to Manual/Automatic/Disabled).
To make the change to the services, I do this:
Get-Service *SQL* | Set-Service –StartupType Disabled
..and verify that they did get changed:
Get-Service *SQL* | Select-Object *
The above code selects all the properties of the service instead of just the key/default ones that PowerShell decides to show.
You could also do something like:
Get-Service *SQL* | Select-Object Name, Status, StartType | Format-Table -AutoSize
Notice above where we select just the properties we want to confirm!
Name Status StartType ---- ------ --------- MSSQL$Inst01 Stopped Disabled MSSQL$Inst02 Stopped Disabled MSSQL$Inst03 Stopped Disabled MSSQL$Inst04 Stopped Disabled SQLAgent$Inst01 Stopped Disabled SQLAgent$Inst02 Stopped Disabled SQLAgent$Inst03 Stopped Disabled SQLAgent$Inst04 Stopped Disabled SQLBrowser Stopped Disabled SQLWriter Stopped Disabled
Everything Together For a Remote Host!
That’s it! Let us see all the commands together had I run it from my Laptop going against a different server host.
#List the SQL Server Services and their state
Get-Service -ComputerName MySQLServerHost *sql*
#Stop the SQL Server Services
Get-Service -ComputerName MySQLServerHost *sql* |
Stop-Service -Force
#Verify if the SQL Server Services got stopped
Get-Service -ComputerName MySQLServerHost *sql*
#Change the StartupType to Disabled (could be Manual/Automatic/Disabled)
Get-Service -ComputerName MySQLServerHost *sql* |
Set-Service –StartupType Disabled
#Verify that the StartupType changed
Get-Service -ComputerName MySQLServerHost *SQL* |
Select-Object Name, Status, StartType |
Format-Table -AutoSize
Checking multiple hosts at once:
If you wanted to check the status of all the hosts on which this change was made, we don’t even have to replace the text each time. We just pipe array of hosts to the service status check. Notice the ForEach-Object which loops through and the “$_” that substitutes the current value of the loop inside.
@('MyHost1','MyHost2','MyHost3','MyHost4','MyHost5') |
ForEach-Object {
Get-Service -ComputerName $_ *SQL* |
Select-Object Name, Status, StartType
} |
Format-Table -AutoSize
You would get back the list for all 4 instances in the 5 hosts like this:
Name Status StartType ---- ------ --------- MSSQL$DEVINST01 Stopped Disabled MSSQL$DEVINST02 Stopped Disabled MSSQL$DEVINST03 Stopped Disabled MSSQL$DEVINST04 Stopped Disabled SQLAgent$DEVINST01 Stopped Disabled SQLAgent$DEVINST02 Stopped Disabled SQLAgent$DEVINST03 Stopped Disabled SQLAgent$DEVINST04 Stopped Disabled SQLBrowser Stopped Disabled SQLWriter Stopped Disabled MSSQL$QAINST01 Stopped Disabled MSSQL$QAINST02 Stopped Disabled MSSQL$QAINST03 Stopped Disabled MSSQL$QAINST04 Stopped Disabled SQLAgent$QAINST01 Stopped Disabled SQLAgent$QAINST02 Stopped Disabled SQLAgent$QAINST03 Stopped Disabled SQLAgent$QAINST04 Stopped Disabled SQLBrowser Stopped Disabled SQLWriter Stopped Disabled MSSQL$PRODINST01 Stopped Disabled MSSQL$PRODINST02 Stopped Disabled MSSQL$PRODINST03 Stopped Disabled MSSQL$PRODINST04 Stopped Disabled SQLAgent$PRODINST01 Stopped Disabled SQLAgent$PRODINST02 Stopped Disabled SQLAgent$PRODINST03 Stopped Disabled SQLAgent$PRODINST04 Stopped Disabled SQLBrowser Stopped Disabled SQLWriter Stopped Disabled MSSQL$BIINST01 Stopped Disabled MSSQL$BIINST02 Stopped Disabled MSSQL$BIINST03 Stopped Disabled MSSQL$BIINST04 Stopped Disabled SQLAgent$BIINST01 Stopped Disabled SQLAgent$BIINST02 Stopped Disabled SQLAgent$BIINST03 Stopped Disabled SQLAgent$BIINST04 Stopped Disabled SQLBrowser Stopped Disabled SQLWriter Stopped Disabled MSSQL$DWINST01 Stopped Disabled MSSQL$DWINST02 Stopped Disabled MSSQL$DWINST03 Stopped Disabled MSSQL$DWINST04 Stopped Disabled SQLAgent$DWINST01 Stopped Disabled SQLAgent$DWINST02 Stopped Disabled SQLAgent$DWINST03 Stopped Disabled SQLAgent$DWINST04 Stopped Disabled SQLBrowser Stopped Disabled SQLWriter Stopped Disabled
There you go folks! Hope you will save a ton of time doing this in the future. If you needed to start all the services back up and change the mode, just pipe to Start-Service instead of Stop-Service and also set the Set-Service mode to Automatic!
Conclusion:
This is not limited to SQL Server and can be done with any Windows Service. You can apply these simple ideas starting right now! You will look like a magician to the uninitiated.
SQL Jana 
Thank you very useful powershell script
Thank you for your feedback Kane!