Since there is no easy way to
migrate a maintenance plan from one SQL Server to another,
most DBAs just recreate all their maintenance plans
on the new SQL Server machine. In this article Greg Larsen shows you a method to
migrate a maintenance plan from one machine to another.
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
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
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:
This command returns output that displays
the plan_id, plan name, and a few other columns. On my server the following is
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
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
Step 4 - Generate T-SQL
Script for SQL Server Agent Job on Source Server
Now that I have the name of
the SQL Server Agent job I can generate a T-SQL script to create the job on the
target server. To generate the script I normally use the "Generate SQL Script
..." function that is available when right clicking on the job in Enterprise
Manager, select the "All Tasks" option, then I save the generated script to a
file to be used in the next step.
Step 5 - Create SQL
Server Agent Job on Target Server
Using the script I generated
in step 4, I can create the 'DB Backup Job for DB Maintenance Plan 'Backup All
Databases'' on the Target Server. To do this, I put the script saved in Step 4
onto a floppy, load the script on my target server, and then execute the script
in Query Analyzer. This will create my maintenance plan database backup job on
the target server.
Step 6 - Create the
Maintenance Plan on Target Server
To create the maintenance
plan on the Target Server I use the sp_add_maintenance_plan SP. I do this by
using the following simple script:
declare @myplan_id uniqueidentifier
exec msdb.dbo.sp_add_maintenance_plan n'Backup All Databases',@plan_id=@myplan_id output
Note I printed the plan_id
that was created. This will be needed in the next step. The maintenance plan_id
I created was "E219D922-9B4F-4C15-BC16-35E0C93CB3EA".
Step 7 - Add databases to
In this step I will be
modifying the maintenance plan I created in Step 6. I will be adding each database
on my Target Server to the maintenance plan by using the sp_add_maintenance_plan_db
SP. Each database will need to be added separately since there is no "All
Database" option for this SP. A series of calls to this SP will be needed to
add each database. Below is a sample of the commands that would be necessary
to add each of the standard SQL Server databases to my target server
exec msdb.dbo.sp_add_maintenance_plan_db 'E219D922-9B4F-4C15-BC16-35E0C93CB3EA',N'master'
exec msdb.dbo.sp_add_maintenance_plan_db 'E219D922-9B4F-4C15-BC16-35E0C93CB3EA',N'model'
exec msdb.dbo.sp_add_maintenance_plan_db 'E219D922-9B4F-4C15-BC16-35E0C93CB3EA',N'msdb'
exec msdb.dbo.sp_add_maintenance_plan_db 'E219D922-9B4F-4C15-BC16-35E0C93CB3EA',N'pubs'
Step 8 - Add SQL Agent
Job to Maintenance Plan
Basically at this point I
have the SQL Server Agent job 'DB Backup Job for DB Maintenance Plan 'Backup All
Databases'' and the maintenance plan 'Backup All Databases' created on the
target server, all that is left to do is associate the SQL Server Agent job
with the Maintenance Plan. To do this I will need to execute the 'sp_add_mainetenance_plan_job'
sp. This sp has the following format:
sp_add_maintenance_plan_job 'plan_id' , 'job_id'
Now from step 6 we already
know the 'plan_id', but we don't know the 'job_id' associated with the 'DB
Backup Job for DB Maintenance Plan 'Backup All Databases' SQL server agent job.
To get the job_id, for this job we need to execute the 'sp_help_job' SP, like
@job_name='DB Backup Job for DB Maintenance Plan ''Backup All Databases'''
From the output generated
when I executed the sp_help_job SP I find that the job_id is '81160E1C-9AA8-4104-9A0A-5463534C08EC'.
So at this point all that is left to associate my job with my maintenance plan is
to execute the following command:
These are all the steps it takes
to migrate a single maintenance plan from a source server to a new target
server. Ok, now I never said it was going to be easy. In reality if you only
need to migrate a single maintenance plan then it might be just as easy to
rebuild the maintenance plan on the new server. Although if you are planning
on migrating a fair number of maintenance plans then building a process to
migrate each maintenance plan using the steps I identified above might a much
easier then building each maintenance plan on the new server manually.
See All Articles by Columnist Gregory A. Larsen