How to use xp_cmdshell in sql server 1

As a DBA, some time we use to write script to perform validation task for database in sql server like SQL Server Health Check reports, SQL Server utilization report also we use to take daily database backups on production. Now main task to move these backup files from production sql server to another secure location on different server so that if anything happens to the current environment we able to rollback using these backup file as base.

 

We decided to set up a process to copy the backup files from  production sql server to another backup server. This process should not hamper production activity so plan to schedule it after database backup activity early in the morning.

 

There is two ways to handle the file transfer from production server to another server.

1. Using windows command in the batch file

2. By using SQL job which will internally call DOS command to transfer the files.

 

Using windows Command

c:\> xcopy “D:\backup\*bak” “\\PreProd\backup\” /S /E /H

 

Above command can be executed through sql job with the help of “xp_cmdshell” command.

This feature is disable by default in sql server due to security concerns.

We can enable by using sp_configure command it use to configure the advance option available sql server instance.

 

Sp_configure ‘xp_cmdshell’, 1

Reconfigure 

 

Changes will not take effect until you run the reconfigure command, it will override the existing value with new value.

DECLARE @ExecCmd varchar(100)

SELECT @ExecCmd = ‘EXEC master.dbo.xp_cmdshell xcopy “D:\backup\*bak” “\\PreProd\backup\” /S /E /H’ 

EXEC(@ExecCmd)

 

Create folders in windows file system by using xp_cmdShell command

Example: DECLARE @ExecCmd varchar(100)

SELECT @ExecCmd = ‘EXEC master.dbo.xp_cmdshell ‘ char(50) + ‘mkdir D:\’CONVERT(varchar(8), getdate(), 112) + ‘\’ + char(50)

EXEC(@ExecCmd)

 

We can extract system level information from systeminfo command on windows server to identify the configuration level settings.

Example: DECLARE @ExecCmd varchar(100)

SELECT @ExecCmd = ‘EXEC master.dbo.xp_cmdshell Systeminfo ‘ 

EXEC(@ExecCmd)

 

One comment on “How to use xp_cmdshell in sql server

  1. Reply hgh reviews Mar 14, 2013 4:26 am

    Hey very nice blog!! Man .. Beautiful .. Amazing .. I will bookmark your blog and take the feeds also

Leave a Reply

  

  

  


1 + 9 =