Professional Microsoft® SQL Server® 2008 Administration

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 ...