Let us take a closer look at the SQL server xp_cmdshell. At Bobcares with the support of our MSSQL support services, we can give you a detailed note on the usage of the xp_camdshell command.

What is xp_cmdshell?

The xp_cmdshell is a very powerful extended procedure that can run command line commands (cmd). T-SQ is very useful for running tasks in the operating system such as copying files, creating folders, sharing folders, and so on.

Enable xp_cmdshell in the SQL server

Firstly, we must enable the xp_cmdshell. We’ll need to check to see if SQL Server’s advanced options are active. Run the following procedure in SQL Server Management Studio (SSMS) to accomplish this:     

USE master; 
 GO 
 EXEC sp_configure 'show advanced option

If the configuration value is 0 when we run the command, it means that Advanced Options are not active. Set the advanced option to 1 to enable the Advanced Options. The following example demonstrates how to do it:

USE master;  
GO  
EXEC sp_configure 'show advanced option', '1';  
RECONFIGURE WITH OVERRIDE;  

Xp_cmdshell is one of the advanced options for SQL server files, and we can now enable it. The following example demonstrates how to do so:

EXEC sp_configure 'xp_cmdshell', 1; 
 GO
 RECONFIGURE;

Copy SQL server backups from one folder to another with xp_cmdshell.

Let us look at an example to understand this process better. The following example will demonstrate how to copy multiple backups from one folder to another. Consider the backup is in the c:backup path. The T-SQL commands below will copy the files from c:Backup to c.    

xp_cmdshell 'copy c:\backup c:\shared';  

To store the full path of files in a folder in a SQL Server table using xp_cmdshell

Now let us go through another example that will store in a SQL Server table all the paths of images stored in a specified file.  

Consider the following scenario: we have a large number of images in the c:image folder. Assume we wish to store the whole pathways in a table as follows:

C:\images\1.jpg
C:\images\2.jpg
…etc.

First, we’ll save all of the folder’s paths in a text file called path for SQL server xp_cmdshell command as shown below:

xp_cmdshell 'dir "c:\images" /s/b>c:\sql\path.txt';  

Following this process, the path.txt will now have all of the image’s full paths. After that we need a table to store the paths:

create table images(path varchar(80)

Use the following sentences to import the information from the txt file into the newly constructed table:   

xp_cmdshell 'bcp AdventureWorKs2016CTP3.dbo.images IN c:\sql\path.txt -c -T'

Bcp is the command that can import images from path.txt into the AdventureWorks2016CTP3 database table dbo.images. The -T option connects via a Trust connection (the current Windows Authentication), while the -c option performs character-type operations.

We can check that we were able to successfully import the data to SQL Server by doing the following selection on the photos table:

select * from images.

Next, we’ll make a table with an ID and a column containing the path and picture name columns. This leads to the final steps in the SQL server xp_cmdshell process:

The final step is to import data from the table images into the table images: Insert (imagename,path) into pictures, then pick replace(path,’c:images’,”) as imagename,path from images. The path and picture name will now be stored in a table: choose * from the images.    

[Need assistance with similar queries? We are here to help]

Conclusion

To sum up we have now seen more about the sql server xp_cmdshell command line with the support of the tech support team.