Page 116 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes To create or manage Maintenance Plans, you must be a member of the sysadmin fixed server
role. Note that Object Explorer only displays maintenance plans if the user is a member of the
sysadmin fixed server role.
To create a maintenance plan using the Maintenance Plan Wizard
1. In Object Explorer, expand a server, and then expand Management.

2. Right-click Maintenance Plans and select Maintenance Plan Wizard.
3. Follow the steps of the wizard to create a maintenance plan.
To create a maintenance plan using the design surface

1. In Object Explorer, expand a server, and then expand Management.
2. Right-click Maintenance Plans and select New Maintenance Plan.

3. In the New Maintenance Plan dialog box, type a name for the plan.
4. The Toolbox opens, and the <Maintenance Plan Name> [Design] surface opens with the
default Subplan_1created. Optionally enter a description for the entire plan on the Design
tab.
5. Double-click Subplan_1, and enter a name and description for the subplan in the Subplan
Properties dialog box. Click the Subplan Schedule icon to enter schedule details in the Job
Schedule Properties dialog box.

6. To build the subplan, drag and drop task flow elements from the Toolbox to the plan
design surface to define the tasks that will be performed. Double-click tasks to open
dialog boxes to configure the task options.
7. To define the workflow between tasks, click the task you want to occur first, hold the
CTRL key down, and then click the task you want to occur second. Right-click either task,
and then click Add Precedence Constraint.

8. To add another subplan that contains tasks run on a different schedule, click Add Subplan
on the toolbar.
9. To add connections to different servers, click Manage Connections.

10. To specify reporting options, click the Reporting and Logging icon. To save more detailed
information, select Log extended information. To write maintenance plan results
information to another server, select Log to remote server. To view the results in the log
file viewer, right-click the Maintenance Plans node or the specific maintenance plan, and
then click

7.4.3 Maintenance Plan Wizard

The Maintenance Plan Wizard helps you set up the core maintenance tasks to make sure that
your database performs well, is regularly backed up, and is free of inconsistencies. The
maintenance plan wizard creates one or more SQL Server Agent jobs that perform these tasks on
local servers or on target servers in a multiserver environment. Execution can be at scheduled
intervals or on demand.
To create or manage maintenance plans, you must be a member of the sysadmin fixed server
role. Note that Object Explorer only displays maintenance plans if the user is a member of the
sysadmin fixed server role.

110 LOVELY PROFESSIONAL UNIVERSITY