Have you ever needed to restart a server and only been able to connect by a SQL Server connection?
Well you are not alone, this situation recently faced me and I knew it was possible to restart a PC via the command line, so why not with XP_CMDSHELL.
—– Enable CMD Shell—–
EXEC sp_configure ‘show advanced options’, 1
EXEC sp_configure ‘xp_cmdshell’, 1
—- Run the shutdown command
EXEC master..xp_cmdshell ‘shutdown -t 30 -r -f’
-r means restart (you could also use s or l to shutdown or logoff respectively
-f Force – use this to ensure the action is forced, you don’t want some application hanging and thwarting the process.
As always in these types of situations it’s important to think outside the box, don’t focus on what you are not able to do, think about other ways you can do something.
In one of my DBA roles I only had RDP access, and SQL access to the servers in our virtualized support environment. Sometimes machines get stuck in a kind of limbo land and if you can’t RDP then the only option is to engage another team to access the virtual machine console to restart.
Well that is no longer a problem thanks to SQL Server and the xp_cmdshell shutdown option.