Migrating a Maintenance Plan from One SQL Server to Another

August 20, 2003

Have you ever had to migrate all of the SQL Server objects from one machine to another SQL Server box? If you have, you probably have found that Microsoft has not provided a method to build a migration script for a maintenance plan. Since there is no easy way to migrate a maintenance plan, most DBAs just recreate all their maintenance plans on the new SQL Server machine. In this article I will show you a method to migrate a maintenance plan from one SQL Server machine to another.

The process I have developed for migrating a maintenance plan is not a simple point and click method. There are a number of steps, both automated and manual, to perform the migration of a maintenance plan. I will walk you through each step. Keep in mind that these steps outline only how to migrate a single maintenance plan. If you want to migrate multiple migration plans then you will need to run through these steps multiple times.

Step 1 - Determine Maintenance Plans on Source Server

This step will determine the maintenance plans that are on the source SQL Server machine. SQL Server has provided the sp_help_maintenance_plan stored procedure (SP) which will display a single maintenance plan or all of the maintenance plans. The syntax for calling this SP is:

sp_help_maintenance_plan [ [ @plan_id = ] 'plan_id' ]

If you specify a plan_id then only that plan will be displayed. If no plan_id is passed then this SP will return all maintenance plans.

Remember maintenance plans are stored in the msdb database. This SP is also stored in the msdb database. To display all the maintenance plans on a server you would execute the following command:

exec msdb.dbo.sp_help_maintenance_plan

This command returns output that displays the plan_id, plan name, and a few other columns. On my server the following is displayed:

From this output you can see that I have two maintenance plans. One called "Backup All Databases" and another called "Backup Master." The "All ad-hoc plans" is a system generated plan, that SQL Server generated, note the plan_id.

Step 2 - Determine SQL Server Agent Job Id for Specific Maintenance Plan

From the output displayed by executing the sp_help_maintenance_plan you can determine the plan_id's for each plan on your source SQL Server machine. Next I need to determine which plan_id I wish to migrate, then issue the following SP replacing <plan_id> with the plan I am going to migrate:

exec msdb.dbo.sp_help_maintenance_plan '<plan_id>'

This command will identify the databases that are referenced in the maintenance plan and the job_id assigned to SQL Server agent Job associated with the plan_id.

From my server I have chosen to display the information associated with the "Backup All Databases" maintenance plan, therefore I would execute the following command:

exec msdb.dbo.sp_help_maintenance_plan 'E6B082F1-6CFD-4DDB-8B31-B031192B02AD'

When I execute the above command I get the following output:

Step 3 - Determine Name of SQL Server Agent Job

From the output generated in step 2, we can see for maintenance plan_id ' E6B082F1-6CFD-4DDB-8B31-B031192B02AD' the SQL Server Agent job associated with this plan has a job_id of '3E19AB39-0F94-465D-909A-1115997C1173'. Passing this job_id to the 'sp_help_job' SP I can determine which SQL Agent job name is associated with this maintenance_plan (plan_id).

When I execute following code:

exec msdb.dbo.sp_help_job '3E19AB39-0F94-465D-909A-1115997C1173'

I get the following output:

From this output you can see that the job name is 'DB Backup Job for DB Maintenance Plan 'Backup All Databases''.