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?

1 Answer 1


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.

  • I started going the maintenance plan route but was encouraged to not do so from this question: dba.stackexchange.com/questions/2561/… Commented May 9, 2011 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. Commented May 9, 2011 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. Commented May 9, 2011 at 19:18

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.