Take the 2-minute tour ×
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.

From this question I learned about a script which creates jobs for backing up databases, checking database integrity, optimizing indexes, and cleaning files. According to the documentation, this should happen in a particular order:

Integrity check after index optimization. This is because index rebuilds sometimes can fix database corruption. Full backup after index optimization. Then the following differential backups will be small. Full backup after the integrity check. Then you know that the integrity of the backup is ok. This means first index optimization, then integrity check and finally full backup.

The script created a job per task. How can I schedule these jobs without having them interrupt each other and ensure that they are in the correct order?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

You can create these jobs using the Maintenance plan wizard, and direct their work-flow. Open up the SQL management GUI (SSMS) then go to: SSMS>Management>Maintenance Plans>New Maintenance Plan. This will open up a subplan editing area in the right-hand side. On your left-hand size, the tool box should also be visible. You can drag and drop components from the toolbox into the subplan area. A little green arrow will be visible for the component dropped into the sublan area, and you can direct these green arrows in the fashion you want your jobs to run. Eg: Create two TSQL statement tasks A and B. Click on A and direct the green arrow from A to point to B. This will ensure that once A finishes, the flow will continue onto B. The whole Maintenance plan can then be scheduled with one start time.

share|improve this answer
    
I started going the maintenance plan route but was encouraged to not do so from this question: dba.stackexchange.com/questions/2561/… –  Michael Hedgpeth May 9 '11 at 18:41
    
For your requirements of backups, index rebuilds, etc... , the MP is a good choice. If you want to code it all out, you may have to use @@error and try catch blocks. –  StanleyJohns May 9 '11 at 19:03
    
I guess it all depends on the size of your maintenance windows - if you have the weekend free, you can do your index rebuilds on a saturday sometime, and the CheckDB on a sunday morning. Full (and transaction log backups) can be done daily in the evenings/dead of night. Maintenance plans are suitable if you have a handful of SQL Servers. If you have more than that, they become a pain to deploy, change, and fix when they break. –  Peter Schofield May 9 '11 at 19:18

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.