How to use the xp_cmdshell extended procedure

Introduction

The xp_cmdshell is a very powerful extended procedure used to run the command line (cmd). This is very useful to run tasks in the operative system like copying files, create folders, share folders, etc. using T-SQL.

In this new article, we will show some useful examples about how to use it.

We will show how to do the following tasks:

How to enable xp_cmdshell
How to copy backups from one folder to another using xp_cmdshell
How to store full path of files of a folder in a SQL Server Table
How to execute PowerShell in T-SQL using xp_cmdshell
How to connect to Azure in SSMS using xp_cmdshell and sqlcmd

Requirements

SQL Server Installed. In this example, we are using SQL Server 2016, but you can work with SQL Server 2005.

Getting started

How to enable xp_cmdshell

First, we will enable the xp_cmdshell. We will need to verify if advanced options in SQL Server are enabled. To do that, run the following procedure in SQL Server Management Studio (SSMS):

USE master;

EXEC sp_configure 'show advanced option'

When you run the command, if the configuration value is 0, it means that Advanced Options are not enabled.

Figure 1. Advanced Options in SQL Server

In order to enable the Advanced Options, set the advanced option to 1. The following example shows how to do it:

USE master;

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE WITH OVERRIDE;

Xp_cmdshell is one of the advanced options, now you can enable this extended procedure. The following example shows how to enable it:

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE;

How to copy backups from one folder to another using xp_cmdshell

The following example will show how to copy several backups from one folder to another. We have several backups in the c:\backup path:

Figure 2. SQL Backups

The following T-SQL statements will copy the files from c:\Backup to c:\Shared folder:

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

The output will be this one:

Figure 3. Copy files output

As you can see, the copy cmd command is copying the files to the shared folder:

Figure 4. Backups in destination folder

How to store full path of files in a folder in a SQL Server table

The next example, will store in a SQL Server table all the paths of images stored in a specified file.

For example, I have the following images in the c:\image folder:

Figure 5. Images in a folder

What I want is to store in a table the full paths like this:

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

We will first store all the paths of the folder in a txt file named path:

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

The path.txt will now include all the image full paths:

Figure 6. Full paths stored

We need a table to store the paths:

create table images(path varchar(80))

To import the information of the txt file of figure 6 into the table just created use the following sentences:

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

Bcp is the command used to import from the path.txt to the table dbo.images in the AdventureWorks2016CTP3 Database. –T is used to connect using a Trust connection (the current Windows Authentication) and –c means to perform character type operations.

If you do a select in the images table, you can verify that we could successfully import the data to SQL Server by doing a select:

select * from images

The SQL Statement will show the following results:

Figure 7. Data imported from a txt to sql using xp_cmdshell and bcp

We are going to create a table with ID and a column with the path and image name columns:

CREATE TABLE [dbo].[pictures](

[id] [smallint] IDENTITY(1,1) NOT NULL,

[path] [varchar](max) NULL,

[imagename] [nchar](40) NULL,

CONSTRAINT [PK_pictures] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The last steps is to import the data from the table images to the table pictures:

insert into pictures (imagename,path) select replace(path,’c:\images\’,”) as imagename,path from images

You will now have the path and image name in a table:

select * from pictures

Figure 8. Table data imported

How to execute PowerShell in T-SQL using xp_cmdshell

PowerShell is used to create scripts and automate tasks in SQL Server, SharePoint, Window Server, Azure and several other Microsoft technologies. PowerShell is now available in Linux also. You can use loops, operators to create powerful scripts to automate tasks using PowerShell.

For example, this cmdlets allow you to copy backups from the c:\sql\source into the destination folder in PowerShell:

copy-item “C:\sql\source” -Destination “C:\sql\destination” -Recurse

The Destination folder will have now the source folder with the backups:

Figure 9. Backups in destination folder

To run the same PowerShell cmdlet using the xp_cmdshell in SSMS, run the following T-SQL statements:

xp_cmdshell ‘powershell -command “copy-item “C:\sql\source” -Destination “C:\sql\destination” -Recurse’

You are calling PowerShell and executing the command to copy all the files and folders from source to destination.

The next example is to run a PowerShell script using xp_cmdshell. First, we will create a script named script.ps1 with the following content:

File_name: script.ps1
copy-item “C:\sql\source” -Destination “C:\sql\destination” –Recurse

To call a PowerShell script in xp_cmdshell, use the following sentences:

xp_cmdshell ‘powershell -command “C:\\sql\\script.ps1″‘
xp_cmdshell and SQL Server PowerShell

Sometimes, we need to connect to SQL PowerShell (SQLPS). The following example will show how to show database information in an html file using xp_cmdshell. The following example shows how to do it.

First, create a script named scriptsql.ps1 with the following content:

sqlserver:
cd sql\InstanceName\default\databases
get-childitem | ConvertTo-HTML | Out-File C:\sql\databases.htm

The script is going to SQL Server and to the Instance name (change to the name of your SQL Server instance), default and databases.

Get-childitem will show all the databases and convert-html out-file will convert the results to HTML format. The result will be saved in a file named databases.htm:

Figure 10. HTML File

How to connect to Azure in SSMS using xp_cmdshell and sqlcmd

Sometimes we need to call Azure from a local Database. Usually to connect to Azure, you need another connection. Xp_cmdshell and sqlcmd is an alternative to create complex stored procedures and scripts connecting to Azure and local databases.

We will first create a SQL Server in Azure Portal. In Azure Portal, go to More Services>SQL Servers:

Figure 11. Azure Portal, SQL Servers

Press Add to add a new SQL Server:

Figure 12. Adding a new SQL Server

Add a new, a login and a password, create a new resource group and select a location and press Create:

Figure 13. SQL Server information

Once created, wait a few minutes and Refresh the list of SQL Servers. In this example, the SQL Server name is sqlshack:

Figure 14. Refreshing Servers

To enable the local machine with SSMS, in Azure Portal, click the SQL Server, go to Firewall and press Add client IP. This will add the local machine with SSMS. Once added, press Save:

Figure 15. Enabling access to Azure by IP

Select the SQL Server created in Azure Portal and select properties. The Azure SQL Server name will be displayed:

Figure 16. Azure SQL Server Name

In your local SQL Server, create a sql script named sqlscript.sql with the following content:

CREATE DATABASE SQLSHACKDB

Create database will be used to create a database named SQLSHACKDB in Azure.

The following T-SQL sentences will be used to run a script in Azure to create an Azure Database:

xp_cmdshell 'sqlcmd -U daniel -S sqlshack.database.windows.net -P "myPWD" -d master -i "c:\sql\sqlscript.sql"'

Where daniel and myPWD are the login and password created in Figure 13. Sqlshack.database.windows.net is the Server name displayed in Figure 16. We are calling the sqlcmd, which is the command line. We are connecting to the master database in Azure and receiving as input the sqlscript.sql script.

If everything is fine, in Azure Portal, in SQL Databases, you will be able to see the Database SQLSHACKDB created:

Figure 17. Azure database created using xp_cmdshell.

Conclusion

In this article, we explained how to copy data from one folder to another, how to copy the full path of files to a SQL table. We also learned how to run PowerShell and how to connect to Azure using xp_cmdshell.

Author
Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

PostgreSQL tutorial to create a user - November 12, 2023
PostgreSQL Tutorial for beginners - April 6, 2023
PSQL stored procedures overview and examples - February 14, 2023