Tell me more ×
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.
up vote 1 down vote favorite

Right now we have a maintenance plan which looks like this:

  • backup all databases
  • clean old backups (> 24 hours)

This job runs every day. Every now and then this script crashes during backup step so we are left with all old backup files.

We want to rewrite this maintenance plan so it runs like this:

  • backup database 1
  • clean old backup for database 1
  • backup database 2
  • clean old backup for database 2
  • etc...

For the first we can use built in actions. Is there any simple approach for the last method described? Or do we have to script it completly?

We don't want to add all databases manually because then we have extra maintenance work everytime somebody adds a new database which also results in potenitially unbackuped databases.

share|improve this question
What is the usual cause of the crash? A standard maintenance plan should handle this easily as long as you're not running out of disk space during backups. – SqlACID Apr 27 '12 at 10:55
the usual cases: not enough space and databases being offline. – amaters Apr 27 '12 at 11:01

2 Answers

up vote 1 down vote accepted

You should probably move from maintenance plans to SQL Agent Jobs and leverage a script like those written by http://ola.hallengren.com/. You would create a Job Step for each database. It will give you the flexibility that you are looking for.

UPDATE: My info above was incorrect. You can use the Ola.Hallengren.com script to do what you are looking for without having to add a job step for each database. After reviewing the script info I saw this:

From: http://ola.hallengren.com/sql-server-backup.html

CleanupTime

Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted.

Backup files are deleted after each database is backed up and verified. Backup files are deleted only if the backup and verification of the database were successful.

DatabaseBackup has a check to verify that transaction log backups that are newer than the most recent full or differential backup are not deleted. This is to guarantee that you can always perform a point-in-time restore.

share|improve this answer
It is one of the requirements that I don't have to do extra work after the script/job has been implemented on the server. The way you describe forces me to add an extra step when somebody adds a database. Something we absolutely want to avoid. – amaters Apr 27 '12 at 14:35
Actually I was incorrect. You could just use the scripts for all user databases which would catch all new databases. I went back and read the script info. It cleans up old backups after each database is backed up. Check here: bit.ly/JwuAat – jgardner04 Apr 27 '12 at 14:39
Exactly what I was looking for. Thanks a lot! – amaters Apr 27 '12 at 14:43
Any time! I am glad I can help. I use that script on all of my production databases. It is a great one. – jgardner04 Apr 27 '12 at 14:47
up vote 0 down vote

Take a look at sp_msforeachdb, it will take a little scripting to fit your needs, something like this. If you're willing to risk having no backup if something goes wrong with the current backup, use WITH INIT, it will just overwrite the current backup and you won't need to do a cleanup phase.

Back to maintenance plans, SQL 2008 I know added "Ignore offline databases", not sure if that's in 2005.

share|improve this answer
Thanks for the pointers. It is not an option to have NO backup. I already know about the sp_msforeachdb. But can I use it for cleaning old backup files? I hardly doubt it. – amaters Apr 27 '12 at 11:35
I would advise not using sp_msforeachdb. @AaronBertrand wrote a piece about its faults, but I can't seem to locate it right now. – Thomas Stringer Apr 27 '12 at 12:38
2  
Here's the link to @AaronBertrand's post: sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/… – Thomas Stringer Apr 27 '12 at 12:41

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.