Thursday, July 14, 2011

Delete backup files older than X days - SQL Server Backups

We use Litespeed for backup compression on one of our production servers and our backup file can only store 2 days worth of full backups and log backups. I have tried scheduling a job to delete the backup files using SQL Server maintenance plan - while it works well with the native backup files, for some reason the job doesn't delete litespeed backups. I did some search and it seems that I am not alone.

I found this thread which has an alternative way of doing this. It is simple. Create a bat file with the below -

forfiles -p "E:\your\backup_drive" -s -m *.* -d -number of days -c "cmd /c del @path"
 If you would like to delete files that are older than 2 days, then it would look like this
forfiles -p "E:\Backups" -s -m *.* -d -2 -c "cmd /c del @path"
For this to work, you need to have forfiles.exe in WINDOWS\systems32 folder.

There are a couple of other ways to delete files older than x days - powershell and VB script.

EDIT: Ah, How did I miss this before? here is a detailed article on mssqltips: