Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 1 down vote favorite

my goal is simple. i want to have SQL keep a nightly backup of a DB for a rolling 5 days.

so each night at some time i want a new DB backup made and one deleted such that i have a rolling 5 days on disk.

i am trying to get a maintenance plan set up to handle this work and have the saving of the file done (i think). i have the backups being appended so a single bak file will contain the rolling 5 days (not sure this will work 0 cause how will SQL know what to delete, and deleting the single bak file will remove all the backups).

as you can tell i am having problems figuring out how to remove older backups fo the DB so that i have only the most recent 5 on disk.

any tips for me on this?

share|improve this question

4 Answers

up vote 3 down vote accepted

You'll have to backup to individual files instead of combining backups. The clean up task isn't smart enough to look inside a single file and remove old backups.

Then drop in a Maintainence Cleanup sub task to remove files older than 5 days.

share|improve this answer
how can i make it so each nightly backup has a date appended to its name? i think i saw this done in the past but cant remember how – kacalapy Oct 19 '10 at 20:37
Once you begin backing up to individual files, each file should automatically have the date and time in the filename. – Simon Peak Oct 19 '10 at 20:43
i am selecting backup to disk option and adding a folder and file name. then i have an overwrite or append option. either way i dont see how this gets me separate bak files daily – kacalapy Oct 19 '10 at 20:51
thanks, i see my problem now. bad selection of options ... – kacalapy Oct 19 '10 at 20:58
up vote 1 down vote

Add a Maintenance Cleanup Task to your maintenance plan. With this task you can choose to delete backup files older than a certain date. I have not tried to do this with a single backup file so I'm not 100% sure if this will work for you.

Is there a reason you want to do it with a single file?

share|improve this answer
i dont care if i have many files, i would actually prefer to have a file for each day... but cant seem to get it to work like this. if this was done the clean up would be simple to delete any files of type .BAK older than 5 days. – kacalapy Oct 19 '10 at 20:38
That seems strange. By default the maintenance plan wizard creates individual files for the databases every day. All you should need is a Back Up Database Task and a Maintenance Cleanup Task to get a quick and dirty backup schedule going. Are you doing something besides this? – Sean Howat Oct 19 '10 at 20:44
Just thought of something. In your Back Up Database Task, make sure you have Create a backup file for every database selected. This is the options that creates individual files if I recall correctly. – Sean Howat Oct 19 '10 at 20:48
up vote 1 down vote

A maintenance cleanup task will do exactly what you are looking for, use the default backup task, with a BAK each day with a time stamp, and add a cleanup task to remove files more than 5 days old.

share|improve this answer
i dont see a "with a time stamp" option – kacalapy Oct 19 '10 at 20:42
up vote 1 down vote

Read this free ebook if you want to know a little bit more about maintenance plans, it wont take much time and its worth it. Brad's Sure Guide to SQL Server Maintenance Plans

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.