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.

  • 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, 2012 at 10:55
  • the usual cases: not enough space and databases being offline.
    – amaters
    Apr 27, 2012 at 11:01

2 Answers 2


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


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.

  • 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, 2012 at 14:35
  • 1
    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, 2012 at 14:39
  • Exactly what I was looking for. Thanks a lot!
    – amaters
    Apr 27, 2012 at 14:43
  • 1
    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, 2012 at 14:47

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.

  • 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, 2012 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. Apr 27, 2012 at 12:38
  • 3
    Here's the link to @AaronBertrand's post: sqlblog.org/2020/05/12/… Apr 27, 2012 at 12:41

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.