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\';