How to restart your sqlserver inside of SQL server

Restarting your SQL service within SQL

we need to bounce the service so that all the tempdb files get created, the agent knows the database config, etc.

The trick isn't stopping the server: "net stop mssqlserver" will do that. The real trick is starting it back up, once you've shut down the SQL server. Since our solution is all done via SQLCMD we needed a way, within SQL itself, to start back up.

Our secret is the ampersand; when the command line interpreter catches it, it views it as you hitting the "Enter" key. So, even though the SQL service is off, the job continues.


declare @sqlcmd varchar(8000)select @sqlcmd = 'net stop SQLSERVERAGENT & ping -n 15 127.0.0.1 & '+ 'net stop MSSQLSERVER & ping -n 15 127.0.0.1 & ' + 'net start MSSQLSERVER & ping -n 15 127.0.0.1 & ' + 'net start SQLSERVERAGENT'EXEC xp_cmdshell (@sqlcmd)