Starting/Stopping SQL Server using Powershell

Published 31 December 09 07:58 PM | MartinBell

One thing I would like to see is more examples of using Powershell in Books Online! For instance the topic Managing Services and Network Settings by Using WMI Provider doesn’t have an example using WMI. So here is one!

# Get an instance of the ManagedComputer object that represents the WMI Provider services.
CD SQLSERVER:\SQL\LOCALHOST
$mc = (get-item .).ManagedComputer
# Reference the Microsoft SQL Server service.
$svc = $mc.Services['MSSQLSERVER']
# Display the state of the service.
'Service status is:' + $svc.ServiceState
# Stop the service if it is running and report on the status
$svc.Stop();
# Wait until the service has time to stop. Then refresh the status
wait-Event -Timeout 10
$svc.Refresh();
# Display the state of the service.
'Service status is:' + $svc.ServiceState
# Start the service again.
$svc.Start();
# Wait until the service has time to start, refresh and display the state of the service.
wait-event -Timeout 10
$svc.Refresh();
'Service status is:' + $svc.ServiceState


The wait-event the service status is incorrectly reported. This is achieved in the books online example by looping until the status changes:

Do Until String.Format("{0}", svc.ServiceState) = "Stopped"
          Console.WriteLine(String.Format("{0}", svc.ServiceState))
          svc.Refresh()
Loop


This uses the SQL Server Provider to get the Managed Computer object, although this is not absolutely necessary, the following script does not use the SQL Server Provider:

# Reference the Microsoft SQL Server service.
$svc=(get-wmiobject win32_service -filter "name='MSSQLSERVER'");
# Display the state of the service.
'Service status is: ' + $svc.State;
# Stop the service if it is running and report on the status
$svc.StopService();
# Wait until the service has time to stop. Then refresh the status
wait-Event -Timeout 10
$svc=(get-wmiobject win32_service -filter "name='MSSQLSERVER'");
# Display the state of the service.
'Service status is: ' + $svc.State;
# Start the service again.
$svc.StartService();
# Wait until the service has time to start, refresh and display the state of the service.
wait-Event -Timeout 10
$svc=(get-wmiobject win32_service -filter "name='MSSQLSERVER'");
'Service status is: ' + $svc.State;

Although the SQL Server Provider makes it very easy to navigate around a SQL Server instance, it may not be available to everyone; therefore for portable scripts you may wish to write without using it. In my next post I will explain why.

Filed under: ,

Comments

# Martin Bell UK SQL Server MVP said on January 1, 2010 03:31 PM:

How much use is Powershell to a DBA when Windows Authentication is not available?

# Martin Bell UK SQL Server MVP said on May 7, 2012 05:56 PM:

Powershell will be even more important when managing SQL Server on Windows Server Core so it's time to build up your toolbox of scripts.

This Blog

SQL Blogs

Syndication