How to delete SQL Server database backups older than a specified date

Introduction

This T-SQL script is used to delete backup files you don’t want to keep in a folder. It will demonstrate how to do achieve it by using master.dbo.xp_delete_file. We will provide the file path, backup file extension and the time line. With the stored procedure in this script, we will delete the backup files older than the time line we entered.

Scenarios

As there are so many backup files in a folder, and we need delete some files older than a specified time. The script will only be used to delete backup files generated by SQL Server.

Script

You can use this script in this way:

1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.

2. Select the specified database and create a “New Query”, copy the code from DeleteBackupFiles.sql, paste it and run.

3. Do a test with parameters: path, file extension, time line.

Note: file extension shall not contain dots (.).

For example:
EXEC DeleteFile 'E:\TestFolder\DeleteFiles','bak','2014-12-31T23:20:00'

Before we run the script and example, the DeleteFiles folder contains 5 files as shown in the picture below:

After running the script and example, we can find that two files has been deleted:

 

 

 

Here are some code snippets for your reference.

SQL
Edit|Remove
mysql
CREATE PROCEDURE DeleteFile
(
@path nvarchar(100),
@FileExtension nvarchar(10),
@Time nvarchar(100)
)
AS
BEGIN
DECLARE @cmd nvarchar(500)
SET @cmd = 'EXECUTE master.dbo.xp_delete_file 0,''' + @path + ''','''+ @FileExtension + ''',''' + @Time + ''', 1'
EXEC(@cmd)
PRINT @cmd
END
GO

CREATE PROCEDURE DeleteFile 
( 
@path nvarchar(100), 
@FileExtension nvarchar(10), 
@Time nvarchar(100) 
) 
AS 
BEGIN 
DECLARE @cmd nvarchar(500) 
SET @cmd = 'EXECUTE master.dbo.xp_delete_file 0,''' + @path + ''','''+ @FileExtension + ''',''' + @Time + ''', 1' 
EXEC(@cmd) 
PRINT @cmd 
END 
GO



 

Prerequisites

SQL Server 2005 or higher version