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 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.

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.

No comments:

Post a Comment

About Me

I have been working with Database technologies for over 16 years, specialized in High Availability Solutions such as Oracle RAC, Data Guard, Grid Control, SQLServer Cluster, SAPHANA. I have experience on wide range of products such as MySQL, Oracle Essbase, Agile, SAP Basis, SharePoint, Linux and Business Apps admin. I have implemented many business critical systems for fortune 500, 1000 companies.