How to create rolling backups for SQL Server 2008 R2

Question

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?

Answer 1

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/

Answer 2

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.

Answer 3

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.

Answer 4

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