Sometimes database professionals need to perform specific tasks at the operating system level. These tasks can be
like copying, moving, deleting files and folders. A use case of these tasks might be removing the old backup files
or copying backup files to a specific directory after a particular time. In SQL Server, we can use
xp_cmdshell extended stored procedure to execute commands directly in the Windows command
prompt(CMD). You need a sysadmin role or proxy account configured to use this extended procedure. We can also use
the SSIS package for the file transfer, but it also requires you to build a package with the relevant tasks.
SQL Server 2019 introduced many new stored procedures, functions, dynamic management views(DMV). In this article, we
will explore the xp_cmdshell procedure along with the new functions in SQL Server 2019 to copy or remove the files.
- Environment details: You should have SQL Server 2019 instance. You can download the latest version from the Microsoft URL
A quick overview of the xp_cmdshell
This extended stored procedure is available in the master database. You need to enable it in the system configuration using the sp_configure.
Enable advanced options in the master database of the SQL Server 2019 instanceUSE master;EXEC sp_configure‘show advanced option’,‘1’;RECONFIGURE WITH OVERRIDE;
Enable Xp_cmdshell extended stored procedureEXEC sp_configure ‘xp_cmdshell’, 1;RECONFIGURE;
- Note: You should be careful in using this stored procedure as it replaces the existing file without any warnings.
You can embed a logic to check the file existence before copying it
Files in the C:NPE folder before deletion
Files in the C:NPE folder after deletion
Suppose we require copying files from one folder to another. We can use the COPY command in the XP_CmdShell for this
purpose. In the below query, we copy all files from the source (C:NPE) to the destination
In the query output, you get a list of all copied files using the xp_cmdshell stored procedure.
‘copy C:NPE C:backups’;
Similarly, we can use the DEL command in the XP_Cmdshell to remove a file from the specified directory. The below
script removes a file script.sql from the C:NPE directory.
EXEC xp_cmdshell ‘del C:NPEscript.sql’
SQL Server 2019 new stored procedures
SQL Server 2019 introduces new stored procedures to copy and delete files using the T-SQL. With the introduction of
these stored procedures, we do not rely on the xp_cmdshell that uses a Windows command shell for command executions.
It provides below new stored procedures to copy and delete files using T-SQL:
Let’s explore these stored procedures in SQL Server 2019.
Copy a single file using the sys.xp_copy_file stored procedure
To copy a single file from the source to the destination folder, we use sys.xp_copy_file stored procedure. Specify
the source and destination path in the query argument.
The below script copies the [script.sql] from the c:script.sql to C:NPE.
To explore the stored procedure further, create a notepad file in the source directory and write some text in it.
Run the below T-SQL and it copies the notepad file into the C:NPE folder.
Once the copy is finished, modify the source file and rerun the above T-SQL. We already have a file present in the
destination directory, but the SQL query executes successfully. You do not get any error message. It shows that
xp_copy_file overwrites the file and does not give any warning or information.
You can see the updated file in the destination folder.
Copy multiple files using the sys.xp_copy_files stored procedure
SQL Server 2019 provides a new stored procedure sys.xp_copy_files. You can use it to copy all files from the source
to the destination folder. You can copy files with a specific pattern as well.
To demonstrate this command, I have removed all files from the destination folder manually.
The below T-SQL copy all files from the source (C:NPE) to the destination
Suppose, we want to copy the files with a specific extension using this stored procedure. We can use an asterisk (*)
wildcard to copy the files satisfying our requirements.
In the destination folder, we verify that it copied SQL extension files in the NPE folder.
Similarly, we copy the files whose name starts with the character C from the source folder.
Delete files using the sys.xp_delete_files stored procedure in SQL Server 2019
SQL Server 2019 introduces new stored procedure sys.xp_delete_files to delete files from the specified folder.
Delete a single file using the xp_delete_files
For copying files, SQL Server 2019 gives two stored procedures xp_copy_file and xp_copy_files. For the delete
operation, it has a single stored procedure xp_delete_files.
To delete a single file, we specify the file name and path in the below T-SQL. It removes the CleanupBuildVersion_RunAfterEveryBuild.sql file from the C:NPE folder.
Delete multiple files using the xp_delete_files
We can delete all files in a specified folder as well using the sys.xp_delete_files stored procedure. For example,
let’s delete all files from the NPE folder using the below query.
In this article, we explored the new stored procedure to copy and delete the files in the SQL Server 2019. It gives
us the flexibility to do specific OS tasks without using the extended stored procedure xp_cmdshell. It is useful and
straightforward to use. You should explore these T-SQL commands in your environment and modify it if it is still
using the xp_cmdshell for copy and delete files.