Everything you need to know about SQL Server Restart with Powershell

SQL Server DBA and Operations staff restart the SQL Server Instances regularly. In a large environment an audited standard approach is the path leading to the greatest DBA time management and a stress free experience.

Powershell is great for service restarts. The Get-Service cmdlet calls the service and the DBA or Operations can monitor the status. It is not much extra work to build in exceptions to notify the DBA on problems. The exceptions could be logged or as a SQL Server restart is critical , they can page support staff.

The WaitForStatus method needs a timeout value passed.  WaitForStatus waits for the SQL Server service to reach the status , and if not , it will timeout.

I’ve defined the $sql_service  with a static value, but there is no reason why these couldn’t be populated dynamically by a search on the services list.

If you have any questions about this Powershell script , contact me .

$sql_service = 'MSSQL$MYSQLINST'

$hrs = 0
$mins = 0
$secs = 30
$timeout = New-Object System.TimeSpan -ArgumentList $hrs, $mins, $secs


#Get  the Microsoft SQL Server service defined by $sql_service variable
$svc = Get-Service -Name $sql_service

# Display the status of the service. 
'Service status is: ' + $svc.Status; 

# 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 
$svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Stopped,$timeout)
'Waiting for Stopped Status'

# Display the status of the service. 
'Service status is: ' + $svc.Status; 

# Start the service again. 
$svc.Start(); 

# Wait until the service has time to start, refresh and display the status of the service. 
$svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Running,$timeout)
'Waiting for Running Status'
'Service status is: ' + $svc.Status;

 Related Posts

Powershell Scripts for DBA

Powershell List all Patches Updates on a Server