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.
Best Answer
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