Enable xp_cmdshell in SQLServer

“xp_cmdshell” is a stored procedure and this will allow to run operating system commands from SQLServer.

You can run below command to get a listing of the backup files under “d:\backup\*.bak”

xp_cmdshell 'dir d:\backup\*.bak '
or
execute xp_cmdshell 'dir d:\backup\*.bak '

You will get below error if xp_cmdshell is not enabled, by default xp_cmdshell is turned off.  

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

The xp_cmdshell can be enabled and disabled by executing “sp_configure” or using the Policy-Based Management.

-- Run below command to allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO

-- Run below command to update the currently configured value for advanced options.
RECONFIGURE
GO

-- Run below command to enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO

-- Run below command to update the currently configured value for this feature.
RECONFIGURE
GO

You should see below output results when you execute the commands

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

Users may receive below backup failure errors when xp_cmshell is disabled.

2012-12-07 06:00:17.03 spid50      Error: 18204, Severity: 16, State: 1.
2012-12-05 06:00:17.03 spid60      BackupDiskFile::CreateMedia: Backup device '\\SQL backup\MSSQL01\Full_Backup\AppCenter\AppCenter_backup_2012_12_07_060001_8233143.trn' failed to create. Operating system error 1330(Logon failure: the specified account password has expired.).
2012-12-07 06:00:17.03 Backup      Error: 3041, Severity: 16, State: 1.
2012-12-07 06:00:17.03 Backup      BACKUP failed to complete the command BACKUP LOG AppCenter. Check the backup application log for detailed messages.

If users are taking database backup over network drive, they may still encounter backup failures after enabling xp_cmshell .

When SQLServer is unable to connect or open connection with shared resource, you still see the backup failures. To resolve the issue create a job using below syntax in SQLServer and schedule the job, it will help to resolve connection with shared resource.

Syntax
 xp_cmdshell 'net use  \\SQLbackup\MSSQL01\ password /User:domain\userid'

Net use command - Connects / disconnects the computer from a shared resource like network drive, mapped drivers.

Refer below link for more information on xp_cmdshell
http://msdn.microsoft.com/en-us/library/aa260689(v=sql.80).aspx
http://msdn.microsoft.com/en-us/library/ms175046.aspx

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com