SQL Server - SQL Server Restart with Powershell

22 August,2012 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server - SQL Server Restart with Powershell


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer