Maintenance task to delete old SQL Server backup files

Problem

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.

Solution

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.

Setup

• 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.
  • Simple script to backup all databases
  • Automating Transaction Log Backups for All Databases