By: Greg Robidoux | Comments (4) | Related: > Maintenance
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.
iDaysOld = 7 |
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.
| C:\DeleteBackupFiles.vbs |
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.
About the author
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 MSSQLTips.com. 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
