Maintenance Plans
Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, is regularly backed up, and is free of inconsistencies. The Maintenance Plan Wizard also creates core maintenance plans, but creating plans manually gives you much more flexibility. In SQL Server 2005 Database Engine, maintenance plans create an Integration Services package, which is run by a SQL Server Agent job. These maintenance tasks can be run manually or automatically at scheduled intervals.
SQL Server 2005 maintenance plans provide the following features:
Workflow creation using a variety of typical maintenance tasks. You can also create your own custom Transact-SQL scripts.
Conceptual hierarchies. Each plan lets you create or edit task workflows. Tasks in each plan can be grouped into subplans, which can be scheduled to run at different times.
Support for multiserver plans that can be used in master server/target server environments.
Support for logging plan history to remote servers.
Support for Windows Authentication and SQL Server Authentication. When possible, use Windows Authentication.
Maintenance plans only run against databases set to compatibility level 80 or higher. The maintenance plan designer in SQL Server Management Studio does not display databases set to compatibility level 70 or lower.
You can migrate SQL Server 2000 database maintenance plans to SQL Server 2005 plans, by right-clicking the SQL Server 2000 database maintenance plan and choosing Migrate.
You must be a member of the sysadmin role to create and manage maintenance plans, and to view them in Object Explorer. The SQL Server Agent node in Object Explorer is only displayed for members of the sysadmin fixed server role, SQLAgentReaderRole, SQLAgentUserRole, or SQLAgentOperatorRole fixed database roles.
For information about how to create a maintenance plan, see How to: Create a Maintenance Plan.
Important
Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent. To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration Services packages, configure SQL Server Agent jobs that run packages to use a proxy account with limited privileges or only add sysadmin members to the db_ssisadmin and dc_admin roles.