Sunday, July 7, 2013

How to restart a server from MSSQL SERVER using xp_cmdshell

Sometimes we need to restart a windows PC or server within or after an interval of time. We can do it easily using 'scheduled task' of windows. Here I‘ll discuss how to do this task from MS SQL SERVER. To do this we'll use a stored procedure ‘xp_cmdshell’ and an ms-dos command ‘shutdown’.

“xp_cmdshell” is a stored procedure which allows us to execute ms-dos commands from SQL Server.
xp_cmdshell {'command_string'} [, no_output]

command_string: command_string for operating system command (ms-dos command). The maximum length of this parameter is varchar(8000) or nvarchar(4000).
No_output: No_output Is an optional parameter. After executing the given command_string with No_output it does not return any output to the client.

Execute following script to enable xp_cmdshell:
use master
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'xp_cmdshell', 1

Now we will use ‘shutdown -r -f -t 01’ as a command_string. 
Shutdown is a dos-command which is used to shut down, restart, log off or hibernate a computer.
 -r for restart, -f Forces running applications to close without warning, -t 01 for restart within 1 second.
Finally we will execute following sql procedure.
EXEC master..xp_cmdshell 'shutdown -r -f -t 01'

If it is required to restart a PC/server every day then we can use MS SQL SERVER JOB.


Post a Comment