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
- SQL Server Installed. In this example, we are using SQL Server 2016, but you can work with SQL Server 2005.
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):
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.
In order to enable the Advanced Options, set the advanced option to 1. The following example shows how to do it:
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;
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:
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:
As you can see, the copy cmd command is copying the files to the shared 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:
What I want is to store in a table the full paths like this:
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:
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:
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
)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:
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:
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:
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:
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:
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:
Press Add to add a new SQL Server:
Add a new, a login and a password, create a new resource group and select a location and press Create:
Once created, wait a few minutes and Refresh the list of SQL Servers. In this example, the SQL Server name is sqlshack:
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:
Select the SQL Server created in Azure Portal and select properties. The Azure SQL Server name will be displayed:
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:
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.
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