For almost all sql backup jobs, we want to have a step to delete old backup files older than a specified time.

Usually there are two ways, one is to use Maintenance Plan or we create our own “cleaning” step using various ways. I am not a big fan of Maintenance Plan mainly because creating a Maintenance Plan is a manual work (using wizard and interface) and thus is not good for large scale deployment. So I’d rather script out my work. In my previous work, I will use xp_cmdshell to run dir command and then get the file names into a table and then analyze the backup file names (yes, each backup file has a date information embedded in the file name) and then do some calculation to choose which files to be deleted.

Now with SQL Server 2008, in sql agent job, we can use Powershell step, i.e. running a powershell script in a job step, so in my backup job, I can have two steps, the first step is the regular backup T-SQL step, and the next step is a PowerShell step just with the following one line of code

Dir x:\backup\* -recurse | where {$_.LastWriteTime –lt (get-date).AddDays(-7) } | % {del $_.FullName}

The script is to delete any files older than 7 days under folder x:\backup\ (including files in the sub-folders if exists because I used –recurse parameter)

Hopefully in near future, MS can provide this deletion function in the natural T-SQL BACKUP sql statement.