Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

VIII.2.5. Automating Things with Maintenance Plans

Setting up and managing all the maintenance jobs that we've shown you in this chapter demands a significant amount of work on the administrator's part. If you've stuck with us this far, you're about to see how managing these sometimes-mundane responsibilities can be made easier with maintenance plans.

It's probably simplest to think of maintenance plans as a collection of events that helps guide SQL Server in automatically executing the administrator's maintenance requirements. Microsoft has made it easy to create and maintain maintenance plans via a collection of graphical tools and wizards. We spend some time to help you understand these helpful assistants.

For the purposes of this simple example, assume that you want to automate a series of steps that culminates in a full backup of a particular database. We show you how to use the SQL Server Maintenance Plan Wizard to make this happen, and then show you how to fine-tune the automatically generated maintenance plan.

1. Launch the SQL Server Management Studio.

2. Connect to the appropriate SQL Server instance.

3. Expand the connection's entry in the Object Explorer view.

4. Expand the Management folder.

5. Right-click the Maintenance Plans folder.

6. Choose the Maintenance Plan Wizard menu option.

   This launches the SQL Server Maintenance Plan Wizard where you specify your requirements.

7. Give your maintenance plan a name, along with an optional description, and click Next.

   You can also create separate schedules ...