xmp_cmd shell usage inside TSQL

Problem: How to run DOS commands from T-SQL.?

Solution:

xp_cmdshell

In order to run this extended stored procedure this requires configuration of server to enable this feature first and the user to execute this stored procedure must have sysadmin permissions. If the user doesn’t have sys admin permission sp_xp_cmdshell_proxy_account to get the privilege.

when using xp_cmdshell you should be very careful as you enable this to communicate with servers you have now the key to access all information, one can fire any command on the particular server to access or delete files.

Example:

Example 1: In this example I would like to show you how we can search for all files with in a particular directory.

As a part of this I would like to change the server configurations using sp_configure.

--trying to see if there are any unsaved options

EXEC sp_configure 'show advanced options',       1

GO

RECONFIGURE

GO

-- enabling xp_cmdshell

EXEC sp_configure 'xp_cmdshell',             1

GO

RECONFIGURE

GO

-- using xp_cmdshell to see all the files and folders in a directory.

exec master.dbo.xp_cmdshell 'dir c:\temp\*'

output

----------------------------------------------------------------------------------------------

 Volume in drive C is Windows

 Volume Serial Number is C47D-9EC0

NULL

 Directory of c:\temp

NULL

03/04/2017  01:16 PM    <DIR>          .

03/04/2017  01:16 PM    <DIR>          ..

02/02/2017  11:12 PM     1,135,456,256 Db.Bak

02/03/2017  05:46 PM         1,212,416 Db_diff.bak

03/04/2017  01:16 PM    <DIR>          SQL Agent Output

03/04/2017  01:03 PM             1,306 tet.sql

               3 File(s)  1,136,669,978 bytes

               3 Dir(s)  235,596,660,736 bytes free

NULL

(14 row(s) affected)

Example 2: In this example I would like to create a new folder without accessing the physical machine to copy a script into the created folder. When you don’t have permission to access the server this can be helpful for copying backups over the servers and from one computer to other computers.

SET NOCOUNT ON;

declare @dt VARCHAR(10),@path VARCHAR(256),@dir VARCHAR(1000), @cmd VARCHAR(1000),@SourceFile VARCHAR(1000)

SET @sourcefile ='C:\Temp\test.sql'

SET @path='c:\temp\DBBackup\'

select @dt= CONVERT(VARCHAR,GETDATE(),112)

SET @dir=@path+@dt

SET @cmd = 'mkdir '+@dir

select @cmd

/*creating directory*/

exec master.dbo.xp_cmdshell @cmd

/* build copy command */ 

SET @Cmd = 'COPY "' + @SourceFile + '" "' + @dir + '"'; 

 select @cmd

/* execute copy command */ 

EXEC master.dbo.xp_cmdshell @Cmd;

OUPTUT:

--------------------------------------------

mkdir c:\temp\DBBackup\20170304

output

--------------------------------------------

NULL

COPY "C:\Temp\test.sql" "c:\temp\DBBackup\20170304"

output

        1 file(s) copied.

NULL