Maintenance task to delete old SQL Server backup files

By:   |   Comments (4)   |   Related: > Maintenance

In two previous tips we discussed how to automate full backups and transaction log backups by creating scripts that iterate through each of your databases and then execute the backup commands.  A reader requested information about how to automate the process of deleting older backup files, so this tip explains one approach for getting rid of older backup files that are generated.

In previous tip we took a look at using Windows Scripting (Simple way to find errors in SQL Server error log) to read through the error log files and generate a slimmer error file with just the error messages and the related messages that were created at the same time.  In this tip, we will also be using Windows Scripting to go through each of the subfolders to find files older than a certain timeframe and then delete these files.

Here is the VBScript code.  This was pulled together from a few different code snippets found on the internet. 

There are two parameters that need to be adjusted:

  • iDaysOld - specify how many days old the files need to be for the script to delete them
  • strPath - this is the folder where the backups are created.
iDaysOld 7

Set objFSO CreateObject("Scripting.FileSystemObject"
Set objFolder objFSO.GetFolder(strPath
Set colSubfolders objFolder.Subfolders 
Set colFiles objFolder.Files 

For Each objFile in colFiles 
If objFile.DateLastModified < (Date() - iDaysOldThen 
MsgBox "Dir: " objFolder.Name vbCrLf "File: " objFile.Name
End If 

For Each 
objSubfolder in colSubfolders 
Set colFiles objSubfolder.Files 
For Each objFile in colFiles 
If objFile.DateLastModified < (Date() - iDaysOldThen 
MsgBox "Dir: " objSubfolder.Name vbCrLf "File: " objFile.Name
End If 


  • To use this script first create a new text file and copy and paste the above code.  Save this new file as C:\DeleteBackupFiles.vbs or whatever you would like to call it.
  • Create another new text file and copy and paste the code below. .(If you rename the file or place it in another folder use this instead of the info below.)  Save this new file as C:\DeleteBackupFiles.bat.

Note: As a safeguard the script just displays a message box with the folder and file name.  To actually delete the files you will need to remove the single quote ' before the two delete lines.:

  • 'objFile.Delete

At this point you can just run the BAT file and this will delete any files in the subfolders. 

The way this works is it will delete any files that it finds in the subfolders after the starting point.  It does not care about what kind of files they are, it will delete all files that are older than the timeframe specified.  The script also will delete files in the root folder and any files one subfolder level deep. It does not go beyond the first subfolder level.

So if you specify your backups to be in "C:\Backup" this script will delete all files in the "C:\Backup" folder as well as any files in the first level subfolders as long as the date of the file is older than specified.

This can now be setup as a scheduled job by calling the BAT file.  SQL Server Agent doesn't like running VBScript files directly, so by using a BAT file you can set this up as a scheduled job.

Next Steps

  • Use this script as is or modify this to have additional options that meet your needs such as specifying certain types of files.
  • The script uses the DateLastModified property, but this could also be changed to the DateCreated property.
  • Modify the script to add some logging, so you can see what has been deleted.
  • The maintenance plans use XP_DELETE_FILE (2005) and SQLMaint (2000) to delete the older files.  You could look at these options as well.
  • Take a look at the other backup scripts to automate your entire backup process.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

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

Friday, April 29, 2016 - 8:00:42 PM - Chris Back To Top (41368)


I have a few SQL Express databases I am backing up using a script I gounf here as well, But I needed a way to clean them up (delete); so this is awesome!  Is there a way to get the specific day as in "Monday", "Tuesday", "Wednesday", etc... from this VB script? I am thinking in terms of having the script run from a Folders named by days of week. For example: Where I could perhaps concatenate  do something like 

Set ObjDayOfWeek = { Result of Some function to get the Day of the week  as in "Monday" }
Set ObjBackupFldr = "C:\SQLBackups\" &  ObjDayOfWeek.  { where the value will be "C:\SQLBackups\Monday\"  }

I'd appreciate your help.

Thank you.


Tuesday, May 29, 2012 - 1:21:17 PM - Michael B Back To Top (17703)

This is even more useful when your main plan dont work etc..


this actually works using ONLY SQL


Just sayin...

Wednesday, December 29, 2010 - 11:09:19 AM - Greg Robidoux Back To Top (12471)

You could use the maintenance task for this.  This is probably more useful for people that are running SQL Server Express.

Wednesday, December 29, 2010 - 10:50:20 AM - Jack Whittaker Back To Top (12470)

Why not just use the Maintenance Cleanup Task provided by that nice Mr Gates as part of your Maintenance Plan?

get free sql tips
agree to terms