Delete old SQL Server backup files with a Stored Procedure

Problem

You want to remove old SQL Server backup files (older than X days) from the backup folder to free drive space. You want to do this using T-SQL and not by using a Maintenance Plan or a Windows Scheduled Task. The advantage of using T-SQL is that you can keep the administrative tasks inside the database server and keep as part of a database back up when you backup the database.  How can this be accomplished using T-SQL?

Solution

I chose a solution that creates a stored procedure called usp_DeleteOldBackupFiles. The procedure gets the backup files folder location (@BackupFolderLocation), the files suffix (@FilesSuffix either BAK or TRN which are the standard for database and log backup files) and the number of days old (@DaysToDelete) to delete the files (this is based on the files modified date).

For example: if a BAK file has a modified date of 03/09/2016 (mm/dd/yyyy format) and the current date is 03/14/2016 then if the @DaysToDelete = 6 the file will not be deleted. If @DaysToDelete = 4 the file will be deleted.

Since the procedure calls the DOS command (forfiles.exe), xp_cmdshell needs to be enabled.

Enable SQL Server xp_cmdshell

use master
go
exec sp_configure 'show advanced options',1
go
reconfigure with override
go
exec sp_configure 'xp_cmdshell',1
go
reconfigure with override
go

You should get the following messages in SSMS after running the above.

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. 
Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

Windows FORFILES command to delete SQL Server Backups

This FORFILES statement executes a command on a file or set of files.

The general syntax of the statement is:

forfiles [/p <path>] [/m <searchmask>] [/s] [/c "<command>"] [/d [{+|-}][{<date>|<days>}]]

We will use the suffix parameter as the search mask /m switch, the folder parameter as the path /p switch and the days parameter as the given value to the /d switch.

Stored Procedure to Delete Old SQL Server Database Backups

CREATE PROCEDURE usp_DeleteOldBackupFiles (
@BackupFolderLocation VARCHAR(30)
,@FilesSuffix VARCHAR(3)
,@DaysToDelete SMALLINT
)
AS
BEGIN
 DECLARE @delCommand VARCHAR(400)
 IF UPPER (@FilesSuffix) IN ('BAK','TRN') 
 BEGIN
  SET @delCommand = CONCAT('FORFILES /p ' ,
    @BackupFolderLocation,
    ' /s /m '  ,
    '*.'   , 
    @FilesSuffix ,
    ' /d '  ,
    '-'   , 
    ltrim(Str(@DaysToDelete)),
    ' /c ' ,
    '"'  ,
    'CMD /C del /Q /F @FILE',
    '"')
  PRINT @delCommand
  EXEC sys.xp_cmdshell @delCommand
 END
END
GO

Example using procedure to delete SQL Server backup files

We want to remove all the .BAK files from folder C:\SQL\Backup and all its sub-folders. We want to remove all .BAK files older than 10 days. The modified date attribute has to be 10 days older than the current date.

use northwind
go
EXEC usp_DeleteOldBackupFiles @BackupFolderLocation='c:\SQL\Backup', @FilesSuffix='bak', @DaysToDelete=10

The result will be all files with a suffix BAK older 10 days from current date are deleted. The stored procedure also prints the command in the messages tab in SSMS and would look like this:

FORFILES /p c:\SQL\Backup /s /m *.bak /d -10 /c "CMD /C del /Q /F @FILE"

Things to note:

  • The procedure was tested with SQL Server 2012 and SQL Server 2014 Developer editions.
  • The xp_cmdshell option needs to be enabled. If the option is not enabled you will get the following error message: Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1 The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’.
  • If the backup folder directory does not exist then nothing occurs (nothing deleted)
  • If no BAK or TRN files older than X days exist then nothing occurs (nothing deleted)
  • Suffix allowed values are BAK and TRN. This protects from accidental deletion of other types of files.
  • A good practice will be to deny execution on this procedure to all database users except the DBA (for security reasons).

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *