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.

Syntax

xp_cmdshell {'command_string'} [, no_output]

Arguments

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

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

Now we will use ‘shutdown -r -f -t 01’ as a command_string. 

Here, 

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.

Categories: MSSQL, Tips