In a previous post, I showed you how one could Restart, Stop and Start the services for an instance of SQL Server using the SSMS GUI. Again, please do not execute this on your PRODUCTION server, but here I am going to show you how to create a batch script that could be used to stop and start your SQL Server service.

Create a new text file using notepad and enter the below statements into your file. Remove, any unneeded statements depending on your instance. If it is a default instance, you can use the first one. If it is a named instance, you’ll use the section for named instances. Save the file with a “.cmd” extension to your desktop or where ever else that you may want to store it!

Script 1: Batch Script

REM Author:  Brian K. McDonald, MCDBA, MCSD
REM Date:      August 2010
ECHO OFF
REM This is for a default instance
NET STOP MSSQLSERVER
NET START MSSQLSERVER
REM Or you can use this one, just remove the REM and any other statements not needed
REM NET STOP "SQL Server (MSSQLSERVER)"
REM NET START "SQL Server (MSSQLSERVER)"
REM Or if you have a named instance use this
REM NET STOP MSSQL$INSTANCENAME
REM NET START MSSQL$INSTANCENAME
PAUSE

To execute the batch file manually, right click the cmd file and click “Run as Administrator”

NOTE: If you receive the “System Error 5 has occurred. Access Denied” error, your OS may require you to run the batch file or the commands using elevated permissions. Running as an administrator should do the trick just fine as shown in figure 1 below.

Figure 1: Sample Manual Execution

You can remove the Pause at the end of the script if you want don’t want any user interaction, but that choice is yours. Again, please do not run this on your production server unless you absolutely need to. Technet does state that all of the data is stored to disc before stopping the service, so that is good, but none-the-less it isn’t good practice just stopping and starting a production instance for no good reason.

Until next time, “keep your ear to the grindstone” – Good Will Hunting

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter