Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Feb 27 '12 at 7:51

This question came from our site for professional system and network administrators.

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.

http://ola.hallengren.com/

share|improve this answer

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.

share|improve this answer
    
@What do you mean expiration renders them unusable - do you mean mean you can't restore the back ups after they expire? –  Shane Feb 29 '12 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.

share|improve this answer
    
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 –  Simon_Weaver Feb 12 '13 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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