I want to run daily backups for a set of databases to disk such that I always have 3 days’ worth of backups kept for each database, with the oldest backup being deleted or overwritten each day. I set up a maintenance plan and set the expiry for the backup set to 3 days. The system uses a timestamp for each file (which I like) but older files are not removed and just build up in the directory, so I'm not sure what expiry means. Is there a way to achieve what I am looking for without having to write re a separate script to clean up the old backups?

4 Answers 4


Ola Hallengren's excellent scripts provide solutions for database maintenance including backups - the scripts have won some awards in the last year, and are increasingly referenced by SQL Server luminaries. I've been using them for about a year - I love them because they are highly configurable - for backups, you define the retention period in the job in hours. The retention period only kicks in if you actually have refresh backups.



How you run them? Expiration does not delete them. If you set that up with a maintenance plan, the maintenance plan has a separate action for cleaning up old files.

Expiration only renders the backup unusable, it does not delete the file. This makes sense - a file can not self-destruct, it needs something deleting it.

  • @What do you mean expiration renders them unusable - do you mean mean you can't restore the back ups after they expire?
    – Shane
    Commented Feb 29, 2012 at 11:49

In the maintenance plan there is another task you can add: Maintenance cleanup task. You can add this task to the current plan to cleanup files older than 3 days. I find that this task is more prompt than the expire date in the backup task.

  • this definitely seems to make the most sense if you're already using a maintenance plan. just be sure to make sure there aren't any backups you need before you run it the first time Commented Feb 12, 2013 at 11:39

One more way to achieve this is to use forfiles.exe to delete files older than x days. We use LiteSpeed for backups and for some reason, the maintenance plan's cleanup task doesn't delete these files. I created a SQL Agent job that executes a bat file which uses forfiles .

forfiles -p "E:\your\backup_drive" -s -m *.* -d -number of days -c "cmd /c del @path"

If you are comfortable with VB script, mssqltips has an article on how to do it.

Relevant SO question here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.