question

Chinmay avatar image
Chinmay asked

PowerShell script to Stop SQL Server services

Hi I am working as a DBA where I need to deal with 1000s of database servers. I would like to use a PowerShell script that will take the server name from a text file and then stop the SQL Server Service and SQL Agent Service. After that product a output file (CSV/Excel) showing: Server Name, Service Name, and Status of service. It will greatly help me as I need to stop the SQL Services once a week for weekly maintenance.
powershellmaintenanceservicessql-server-service
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image Shawn_Melton commented ·
What weekly maintenance requires that you restart SQL Server services?
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This site works off of voting. Indicate all helpful answers by clicking on the thumbs up next to those answers. If any one post solved the problem indicate this by clicking on the check box next to that answer.
0 Likes 0 ·
sqlaj 1 avatar image
sqlaj 1 answered
Powershell can be intimidating. Look at these links for more information and a few examples. > **Get-Service** > http://technet.microsoft.com/en-us/library/hh849804(v=wps.620).aspx This one shows how to run against a server list. > **Learn How to Run PowerShell Scripts Against Multiple Computers** > http://blogs.technet.com/b/heyscriptingguy/archive/2010/12/30/learn-how-to-run-powershell-scripts-against-multiple-computers.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Chinmay avatar image Chinmay commented ·
Hi Thanks for response but these links only showing how to get the services from a particular computer.I tried these codes to show the sql service but it showing for local computer only.Can you modify to show a list of remote servers. $file1 = Get-Content "D:\PowerShell\Srinivas_Diskspace_Powershell\ computers.txt" | %{ get-wmiobject -Class win32_service | where {$_.name -like '*SQL*'} } | select-object systemname,Name,state,startmode $file1 | export-csv -path "D:\PowerShell\Chinmay.csv"
0 Likes 0 ·
Shawn_Melton avatar image
Shawn_Melton answered
Although I do not see any reason that would require you to restart the services on a weekly basis, especially for 1000s of servers. I would suggest using Get-Service instead of WMI. It offers the same information but allows you to then pass those objects into Stop-Service and Start-Service. So you can just use this to get the status of the services on your servers: $servList = gc Mylist.txt Get-Service -Name *SQL* -Exclude *AD*,*Browser,*Writer -ComputerName $servList | Select @{Lable="ServerName";Expression={$_}}, Name, DisplayName, Status I could add how to go about doing the start/stop with the output of above, however I am going to refrain from it. You need to understand what it does and how, and the best way is by figuring that part out on your own.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

fade2blackuk avatar image
fade2blackuk answered
Take a look at my blog post [ http://sqldbawithabeard.com/2013/05/19/checking-sql-error-logs-event-logs-and-stopping-services-with-powershell/][1] That uses Get-Service alternatively make use of my services check script # Enter Server and save as variable $Server= Read-Host "Please Enter the Server" # Get Wmi for those variables, choose start mode of auto and display in grid Get-WmiObject Win32_Service -ComputerName $Server | Where-Object { $_.StartMode -like 'Auto' }| Select-Object __SERVER, Name, DisplayName, StartMode, State |Sort-Object State -desc| Format-Table -auto Get-WmiObject Win32_Service -ComputerName $Server | Where-Object { $_.DisplayName -like '*SQL*' }| Select-Object __SERVER, Name, DisplayName, StartMode, State | Format-Table -auto Get-WmiObject Win32_Service -ComputerName $Server | Where-Object { $_.StartMode -like 'Auto' -AND $_.State -notlike 'Running'}| Select-Object __SERVER, Name, DisplayName, StartMode, State |Sort-Object State -desc| Format-Table -auto To run these against multiple servers. Create a SQLServers.txt file with a server on each line then run $SQLServerTXT = "Path to SQLServers.TXT File" $Servers = Get-Content $SQLServersTXT foreach($Server in $Servers) { Put code in here and use the $Server variable ie get-service -ComputerName $server|Where-Object { $_.Name -like '*SQL*' } } [1]: http://sqldbawithabeard.com/2013/05/19/checking-sql-error-logs-event-logs-and-stopping-services-with-powershell/ As always, Check and test all scripts for your own production environment to ensure they do what you want without any unforeseen impact
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
Great beard and outstanding JOGLE ride!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.