Delete old SQL Server backup files with a Stored Procedure

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

   Free MSSQLTips webinar - "Efficient Monitoring and Management of SQL Server" (click to register)


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

Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights

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

Article Last Updated: 2016-10-13

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