SQL Backup Compression & File Cleanup

SerkTools did a nice job discussing how to compress SQL backups using GZIP so I won't cover that.  The article can be found here.

The problem with compressing the backups in SQL 2005 and prior is that SQL doesn't know how to clean those files up.  There are other places in my environment that I need to do housekeeping so I have scripted a .vbs application that can be scheduled with the task scheduler.  My general rule of thumb is: any server, service, application or process must clean up after itself.

Here's my code:

dim path, interval, duration, fso, folder, x, y

path = "D:\MSSQL\Backup"
interval = "H"                                   
duration = 25 ' 25 is intentional due to an assumed rounding issue

set fso = CreateObject("Scripting.FileSystemObject")
set folder = fso.GetFolder(path)
for each x in folder.SubFolders

    for each y in x.Files

        if DateDiff(interval, y.DateLastModified, Now) >= duration and Weekday(Date) = 2 then y.delete

        ' This file should run only on Monday.  If it is run any other day it will
        ' not delete any files.  This is by design.  --BMO

    next
next
set y = nothing 

set x = nothing 

set folder = nothing 

set fso = nothing

I create weekly full backups early Monday morning.  I create differential backups every other day of the week and I capture transaction log backups throughout the day.  Prior to switching from daily fulls and compressing the backup jobs they consumed ~665 GB per week.  Now they consume only 29 GB (95+ % less space).  That's why I run my cleanup job only once per week.

I want the job run on Monday to ensure all my backups have had an opportunity to be written off-site and to tape.  There was nothing from stopping the job from being run on the wrong day so I added the bit of code that checks the day of the week.