Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Delete old SQL Server backup files with a Stored Procedure


By:   |   Last Updated: 2016-10-13   |   Comments (5)   |   Related Tips: 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.



    



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? 


Learn more about SQL Server tools