The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations and can be enabled by using the Policy-Based Management or by running the sp_configure system stored procedure as shown in the following code example:
— To allow advanced options to be changed.
EXEC sp_configure ‘show advanced options’, 1
— To update the currently configured value for advanced options.
— To enable the feature.
EXEC sp_configure ‘xp_cmdshell’, 1
— To update the currently configured value for this feature.
The following link illustrates how to use the xp_cmdshell command to export data from a table in the database to a flat file:
This example shows how to copy a scheduled backup job from one instance of SQL server 2005 to another.
In Microsoft SQL Server Management Studio, expand the SQL Server Agent tree.
Expand the jobs tree.
Here you will see a list of all the jobs currently stored on the server.
Right-click on the job that you want to copy over to a second server.
Select Script Job as –> Create To –> New Query Editor Window.
This will open a new window in the management studio containing auto-generated code.
Connect to the Second server, which will be the destination for the copied job.
Open up a new tab in the management studio – Copy the entire auto-generated code from the previous step and paste it into the new tab.
Below is the auto-generated code ready to execute on the new server.
Execute the whole code to continue.
Refresh and expand the Jobs tab on the second server, all being well the copied job will be displayed in the job list.
Right-click the job and select Properties to view the details.
You will see that the steps, schedules, alerts, notifications etc. will have been copied across. The job will now run on server 2 in the same way as it ran on server 1.