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.