mssqltips logo

Delete old SQL Server backup files with a Stored Procedure

By:   |   Updated: 2016-10-13   |   Comments (6)   |   Related: More > Backup

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


Last Updated: 2016-10-13


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, August 15, 2019 - 9:33:31 PM - James Back To Top

Why not use the built in " master.dbo.xp_delete_file " ?

https://stackoverflow.com/questions/24582996/sql-server-xp-delete-file-parameters


Tuesday, January 23, 2018 - 11:11:09 PM - Nani Back To Top

 

 Hello Admin,

above Stored procedure is not supporting for UNC share paths ,

can you please send sample code for UNC share folders ,

i want to delete .bak files from UNC share , for my case above sp is not aupporting ,

 

 


Friday, April 28, 2017 - 1:05:39 PM - Perry whittle Back To Top

Favouring a tsql script which requires xp_cmdshell is not my idea of automation.

you can keep this inside the database engine and invoke a PoSh command on an agent job, uses about 4 lines of code


Thursday, October 13, 2016 - 9:13:29 PM - Perry whittle Back To Top

Just use powershell it's s lot easier and you don't need xp_cmdshell


Thursday, October 13, 2016 - 7:02:32 PM - Jeff Moden Back To Top

 @Patrick McVey... you must be a kindred spirit WRT xp_CmdShell.


Thursday, October 13, 2016 - 2:38:48 PM - Patrick McVey Back To Top

Why is xp_cmdshell disabled in the first place? 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools