You are here:   Blog
Register   |  Login

Blog Archive:

* Can be used in order to search for older blogs Entries

Search in blogs

Blog Categories:

* Can be used in order to search for blogs Entries by Categories

Blog Tags:

* Can be used in order to search for blogs by keywords


Awared MVP


Microsoft® Community Contributor 

Microsoft® Community Contributor

 Read this before you use the blog! Maximize

Recent Entries


Written by: ronen ariely
15/04/2022 04:34 RssIcon


Delete old backup files can be done by running transact SQL query from a job that we schedule.

In this post I am focusing on the transact SQL script and assume that you already familiar with creating and schedule a job that run T-SQL scripts. For more information regarding jobs, you can check Microsoft documentation here.

Deleting files using transact SQL query can be done in multiple approach using external languages and OLE object, built-in undocumented Extended Stored Procedures and more. 

In this post I will go over some of the options we can use. This post is not a deep dive into the solutions but a review. For more information I am adding relevant links to each option I mention.


Before we starts

It is important to find the right files to delete or and confirm that the files exists. SQL Server 2017 introduces the following new dynamic management views (DMVs) and dynamic management functions (DMFs) which can be use for this task before and after we delete our backup files.

  • sys.dm_os_enumerate_fixed_drives
  • sys.dm_os_enumerate_filesystem
  • sys.dm_os_file_exists


Option 0: SQL Server Maintenance plan

SQL Server Maintenance plans has a built-in option to delete old backup files by using the cleanup task.

1. Open SSMS object explorer window -> Expand Management folder -> Right click on Maintenance Plans -> Click on Maintenance Plan Wizard

2. In the Maintenance Plan Wizard, click Next

3. fill the information for your maintenance plan and click Next

4. In the "Select Plan tasks" window, check Maintenance Cleanup Task and click on Next.

5. In the "select maintenance task order" window, click next

6. In the next windows you will get the option to configure the maintenece task. Here you can set the parameters for the files to be deleted

For more information and full totrial you check this toturial:


More information from Microsoft can be found here:



Option 1: run Windows command shell

We can use the built in Extended Stored Procedures xp_cmdshell to spawn a Windows command shell. For more information about using xp_cmdshell you can check Microsoft Documentation here.

xp_cmdshell 'del "C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\DB2.bak"'

You can use command shel to first find the files you want to delete. 

Features: documented solution, same security rights as the SQL Server service account, disabled by default, 


Option 2: Using SQLCLR

SQL Server supports running .NET Common Language Runtime (CLR) code in a virtual runtime environment (managed code). We can create stored procedures or user-defined functions to check the files and delete the old backup files. For more information about using CLR in SQL Server you can start here.

Features: documented solution, disabled by default, 


Option 3: call a method of an OLE object


Features: documented solution, disabled by default,


Option 4:run Python or R script


Features: SQL Server 2016 and above, documented solution, disabled by default,


Option 5: call Java runtime


Features: SQL Server 2019 and above, documented solution, disabled by default,


Option 6: sys.xp_delete_file

SQL Server supports undocumented Extended Stored Procedures (implemented as a function inside the DLL) to delete a file: xp_delete_file. This procedure takes five parameters:

  • File Type = 0 for backup files or 1 for report files.
  • Folder Path = The folder to delete files.  The path must end with a backslash "\".
  • File Extension = This could be ‘BAK’ or ‘TRN’ or whatever you normally use.
  • Date = The cutoff date for what files need to be deleted.
  • Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.
EXEC master.sys.xp_delete_file
    'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\test\',

Features: undocumented, disabled by default,

Option 7: sys.xp_delete_files

SQL Server 2019 introduced new undocumented Extended Stored Procedures (implemented as a function inside the DLL) to manage file: sys.xp_copy_file; sys.xp_copy_files; sys.xp_delete_files

This sys.xp_delete_files supports standard DOS wildcard characters, which allow you to do exactly what you want - not delete files starting with FG for example. It also supports multiple fully-qualified path specifications at once.

IMPORTANT!!! sys.xp_delete_files is much more flexible but also more dangerous! It allows for example to delete all types of files and not only backups.

This function takes the following 2 parameters: @initial_directory nvarchar(255) and @search_pattern nvarchar(255) and it returns the information about all the files and folders which fit the wildcard condition. It uses the same wildcard condition as the new sys.xp_delete_files which makes it highly useful to verify what you are going to delete before you actually execute the delete

Copy single file:

EXEC master.sys.xp_copy_file 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\sdfasdfasdfasdf.txt', 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\sdfasdfasdfasdf2.txt';

Copy all files in folder

EXEC master.sys.xp_copy_files 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\*', 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\temp\';

Delete single file

EXEC master.sys.xp_delete_files 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\test\DB.bak';

Delete files using specific format

EXEC master.sys.xp_delete_files 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\temp\DbName*.bak';

Delete all Files in folder

EXEC master.sys.xp_delete_files 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\temp\*';

Delete Folder

EXEC master.sys.xp_delete_files 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\temp\'

More sample you can see at my answer on the Microsoft Forum: https://learn.microsoft.com/en-us/answers/questions/587526/delete-old-backup-files

Features: undocumented, enable by default, SQL Server 2019 and above,