Recipe for a good, solid maintenance plan

Hello

I'm in the proces of a major revision of the maintenance plans for the SQL servers in our company, and in connection with that I would like to hear how other people are doing this.

Here's a quick rundown of the plan:

Critical DBs will be backed up on tape (daily incremental, weekly full, w. Veritas Backup Exec 9.0). Also, there will be full daily disk backups for easy quick recovery. These will be done locally, as I have bad experiences trying to backup across a network share.

DBs of medium importance will be backed up fully every day on disk. These BAK-files will then be backed up on tape, if I find it necessary.

Although I rarely restore from the tapes, I think they're nice to have in case the office burns down or who knows what.

The maintenance plans will be split into 3:
1. System DBs maint. plan
2. Critical importance DBs maint. plan
3. Medium importance DBs maint. plan

The more I think about it, the more I think I might just classify all production DBs as critical and all test DBs as medium. Maybe that would make more sense.

For all disk backups, optimization and integrity checks (and backup) will be done daily. For DBs of critical importance (eg. production DBs) Transaction log back will be done as well. What's a good schedule for this? Once every 3 hours or so? Every hour? How much burden does this operation put on the server?

I guess that's about it so far. If anyone has any suggestions or comments, I would be very pleased to hear them.

MNJFrequency of trx. log backups depends on the level of activity of action queries and recoverability requirements. In one of our databases here we're doing 15-minute trx. log dumps and the resulting file varies from 800MB to 2.5GB in size. Another database barely creates a 100K logs but we're doing dumps every 30 minutes for its point-in-time recoverability requirements. It all depends.

As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)|||[i]As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)

Why is it beneficial to backup the system DBs last? Since I keep system and user DBs separated into different maint. plans, I guess I can just schedule the system maint. plan to occur 15 min. after the user main. plan?

MNJ|||For one, if MSDB is backed up last, - it will contain the latest backup information of all other databases, as well as itself. This information is available when looking at the General tab of database Properties window.

As per scheduling, - as I said earlier, I have execution of all maintenance plans in one batch with SQLMAINT. If you're using Scheduled Tasks, then you can add a step with SQLMAINT -PlanName <sys_db_maint_plan> after your application databases.|||Good point with system DBs, I will take that into consideration. I suppose if I just make sure to schedule them a bit apart, it should work out ok.

MNJ