אפר15
Written by:
ronen ariely
15/04/2022 04:34 
Introduction
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:
https://solutioncenter.apexsql.com/how-to-delete-old-database-backup-files-automatically-in-sql-server/
More information from Microsoft can be found here:
https://learn.microsoft.com/en-us/sql/relational-databases/maintenance-plans/use-the-maintenance-plan-wizard?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699
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"'
GO
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
https://www.mssqltips.com/sqlservertip/5655/script-to-delete-old-sql-server-backup-files-using-msdb-backup-history-data/
Features: documented solution, disabled by default,
Option 4:run Python or R script
https://learn.microsoft.com/en-us/sql/machine-learning/tutorials/quickstart-python-create-script?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699
Features: SQL Server 2016 and above, documented solution, disabled by default,
Option 5: call Java runtime
https://learn.microsoft.com/en-us/sql/language-extensions/how-to/call-java-from-sql?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699
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.
DECLARE
@MyDate DATETIME = DATEADD(
day
, 1, GETDATE())
EXEC
master.sys.xp_delete_file
0,
'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\test\',
'
BAK',
@MyDate,
0;
GO
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,