How to: Migrate SQL Server 2000 Database Maintenance Plans

SQL Server 2000 database maintenance plans were Transact-SQL commands run by SQL Server Agent jobs. In SQL Server 2005 or later versions, maintenance plans are created as SSIS packages, which are also run by SQL Server Agent jobs. After upgrading SQL Server 2000 to SQL Server 2005 or later, the SQL Server 2000 database maintenance plans are visible under the Legacy node of the Management folder in Object Explorer. These database maintenance plans continue to work as they did before upgrading. You can maintain these database maintenance plans by right-clicking a plan and then clicking Properties.

To migrate the SQL Server 2000 database maintenance plans to the SQL Server 2005 or later version, right-click the database maintenance plan, and then click Migrate. To migrate database maintenance plans, you must be a member of the sysadmin fixed server role.

To migrate SQL Server 2000 database maintenance plans

  1. In Object Explorer, expand a server, expand Management, expand Legacy, and then expand Database Maintenance Plans.

  2. Right-click a database maintenance plan, and click Migrate.

  3. When the migration finishes, a SQL Server 2005 or later version of the plan is placed in the Maintenance Plans folder. Note that the SQL Server 2000 version of the plan remains in the Legacy folder and is still enabled.

  4. Test the migrated plan in the Maintenance Plans folder before you enable it, and then delete the SQL Server 2000 plan in the Legacy folder.

Community Additions