In SQL Server 2019, you can use the sys.xp_delete_files stored procedure to delete a file on the file system.
This stored procedure was introduced in SQL Server 2019, and it can be used in conjunction with sys.xp_copy_file and sys.xp_copy_files (both of which were also introduced in SQL Server 2019), 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 and delete files without relying on xp_cmdshell.
Example 1 – Delete 1 File
Here’s an example of deleting a single file.
Example on Linux:
EXEC master.sys.xp_delete_files '/var/opt/mssql/data/samples/albums2.csv';
I ran this code on my Mac, which uses SQL Server 2019 on Linux. This successfully deleted a file called albums2.csv (full path /var/opt/mssql/data/samples/albums2.csv).
To copy files on a Windows system, you’d need to use the Windows path convention.
Example on Windows:
EXEC master.sys.xp_delete_files 'D:\mssql\data\samples\albums2.csv';
Obviously, the exact location will depend on your situation.
Example 2 – Delete Multiple Files
You can use the same procedure to delete multiple files.
Example on Linux:
EXEC master.sys.xp_delete_files '/var/opt/mssql/data/samples/final/albums*.csv';
In this case, I deleted all files in the /var/opt/mssql/data/samples/final directory 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.
Example on Windows:
EXEC master.sys.xp_delete_files 'D:\mssql\data\samples\final\albums*.csv';
Example 3 – Delete Folders
You can use the same procedure to delete the whole folder.
Example on Linux:
EXEC master.sys.xp_delete_files '/var/opt/mssql/data/samples/final/';
In this case, I deleted the /var/opt/mssql/data/samples/final directory altogether.
Example on Windows:
EXEC master.sys.xp_delete_files 'D:\mssql\data\samples\final\';