Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Maintenance task to delete old SQL Server backup files


By:   |   Read Comments (4)   |   Related Tips: More > Maintenance


Free MSSQLTips Webcast Today >> Optimize SQL Server Performance


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
strPath 
"C:\BACKUP"

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
       
'objFile.Delete 
   
End If 
Next 


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
           
'objFile.Delete 
       
End If 
   Next 
Next 

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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 Hi, 

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

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

 

this actually works using ONLY SQL

 

http://www.sqlservercentral.com/scripts/Administration/62729/

 

Just sayin...


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

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

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


Learn more about SQL Server tools