Two new stored procedures introduced in SQL Server 2019 are sys.xp_copy_file and sys.xp_copy_files, which enable you to copy files.

Prior to SQL Server 2019, you would need to use xp_cmdshell, which spawns a Windows command shell and passes in a string for execution. The new stored procedures introduced in SQL Server 2019 allow you to copy files without relying on xp_cmdshell.

Example 1 – Copy 1 File

You can use sys.xp_copy_file to copy a single file. Using this stored procedure allows you to also name the new file.

Example on Linux:

EXEC master.sys.xp_copy_file
'/var/opt/mssql/data/samples/albums.csv',
'/var/opt/mssql/data/samples/albums2.csv';

I successfully ran this code on my Mac, which uses SQL Server 2019 on Linux.

To copy files on a Windows system, you’d need to use the Windows path convention.

Example on Windows:

EXEC master.sys.xp_copy_file
'D:\mssql\data\samples\albums.csv',
'D:\mssql\data\samples\albums2.csv';

Example 2 – Copy Multiple Files

You can use sys.xp_copy_files to copy multiple files.

Example on Linux:

EXEC master.sys.xp_copy_files
'/var/opt/mssql/data/samples/albums*.csv',
'/var/opt/mssql/data/samples/final';

Here I’m in the same folder as the previous example. In this case, I copied all files that start with albums and end with .csv. I use the asterisk (*) wildcard to select files that may include other characters after the albums part.

This example copies the files to the /var/opt/mssql/data/samples/final directory.

Example on Windows:

EXEC master.sys.xp_copy_file
'D:\mssql\data\samples\albums*.csv',
'D:\mssql\data\samples\final';

Deleting Files

SQL Server 2019 also introduced the sys.xp_delete_files stored procedure, which enables you to delete files. See How to Delete Files in SQL Server 2019 for examples of deleting the files created in this article.