Delete old SQL Server backup files with a Stored Procedure

By:   |   Comments (6)   |   Related: > Backup


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?


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
exec sp_configure 'show advanced options',1
reconfigure with override
exec sp_configure 'xp_cmdshell',1
reconfigure with override

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
 DECLARE @delCommand VARCHAR(400)

 IF UPPER (@FilesSuffix) IN ('BAK','TRN') 
  SET @delCommand = CONCAT('FORFILES /p ' ,
    ' /s /m '  ,
    '*.'   , 
    @FilesSuffix ,
    ' /d '  ,
    '-'   , 
    ' /c ' ,
    '"'  ,
    'CMD /C del /Q /F @FILE',

  PRINT @delCommand
  EXEC sys.xp_cmdshell @delCommand

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
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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

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

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

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


 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 (55284)

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 (43562)

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 (43560)

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

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

Why is xp_cmdshell disabled in the first place? 

get free sql tips
agree to terms