O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

5.1. Maintenance Plans

Maintenance plans are a quick and easy way to automate routine maintenance tasks in SQL Server. They are no more than a user interface on top of regular SQL Server Agent jobs. However, the tasks in a plan aren't equivalent to job steps, as maintenance plans are built using SQL Server Integration Services (SSIS), and these are then run as a single SSIS job step in a job that maps to the maintenance plan name. For routine maintenance tasks they may be all that you need to automate on many SQL Servers.

There are two ways to create maintenance plans. The quick and easy way is to use the Maintenance Plan Wizard, and the manual way is to use the Maintenance Plan Designer.

5.1.1. Maintenance Plan Wizard

This section walks you through the steps of creating a backup using the Maintenance Plan Wizard:

  1. The first step is to launch the Wizard, which lives on the context menu on the Maintenance Plans node in the Object Explorer in SQL Server Management Studio. Selecting the Maintenance Plans Wizard menu item will launch the first page of the Wizard.

    I usually opt to not show this page again, and then select Next. This brings up the Select Plan Properties page, where you can set some of the Plan options.

  2. On this page, as shown in Figure 5-1, you specify a name and description for the plan and select the scheduling options. For the example in this chapter, stick with the default settings and create a manually scheduled plan.

    Figure 5-1. Figure 5-1
  3. Select Next to move on ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required