אפר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.

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.

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:

Copy all files in folder

Delete single file

Delete files using specific format

Delete all Files in folder

Delete Folder

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,