Introduction

SQL Server’s Enterprise Manager usually provides the Generate SQL Script… option to make a script for creating a database object as shown in the example below:

Scripting is the most reliable way to move an object from one server to another. Unfortunately, Database Maintenance Plans do not provide an option for generating scripts. It would be better to have a script than to run the Database Maintenance Plan Wizard on each and every server on which a plan needs to be created.

It is eminently possible to develop a script that will create a Database Maintenance Plan, because a plan is really a collection of SQL Server Agent jobs. Although plans do not offer the Generate SQL Script… option, jobs do. Once you have scripts for the jobs, you can consolidate them into a single script to create the plan. The challenge is in figuring out exactly how to do this. It is not difficult, but it is tedious and requires careful attention to detail.

Database Maintenance Plan Metadata

A Database Maintenance Plan is defined in three tables in msdb:

1. sysdbmaintplans
2. sysdbmaintplan_databases
3. sysdbmaintplan_jobs

The sp_help_maintenance_plan and sp_help_job stored procedures provide a means for you to obtain all of a plan’s metadata.

Scripting the SQL Agent Jobs

Although it is possible to create a Database Maintenance Plan without any SQL Server Agent jobs, any plan with one or more scheduled activities will have SQL Server Agent jobs. It’s easy to script SQL Server Agent jobs, so that’s a good place to start.

Begin by finding out what SQL Server Agent jobs need to be scripted. As Server Administrator, you’ll use two stored procedures in msdb to find the names of all of the Agent jobs.

1. Execute sp_help_maintenance_plan to find the plan_id for your Database Maintenance Plan. The plan_id is a globally unique identifier (guid), also known as a uniqueidentifier data type.

use msdb
exec sp_help_maintenance_plan

plan_id                              plan_name       
------------------------------------ ----------------
00000000-0000-0000-0000-000000000000 All ad-hoc plans
D0906776-0B68-4F64-9081-5754B05057BC Northwind Backup

2.  Execute sp_help_maintenance_plan again, this time passing it the plan_id obtained in the previous step.

use msdb
exec sp_help_maintenance_plan
'D0906776-0B68-4F64-9081-5754B05057BC'

plan_id                              plan_name       
------------------------------------ ----------------
D0906776-0B68-4F64-9081-5754B05057BC Northwind Backup

database_name
-------------
Northwind

job_id
------------------------------------
71A3FF3B-72C2-4FAE-BA7B-B12F6CFB8DF0
417A64E1-F3F5-4905-A811-E2E3338AE3E2

3. Get the names of the SQL Server Agent jobs by executing sp_help_job for each job_id found in the previous step.

use msdb
exec sp_help_job '71A3FF3B-72C2-4FAE-BA7B-B12F6CFB8DF0'
exec sp_help_job '417A64E1-F3F5-4905-A811-E2E3338AE3E2'

name
---------------------------------------------------------------
Integrity Checks Job for DB Maintenance Plan 'Northwind Backup'
DB Backup Job for DB Maintenance Plan 'Northwind Backup'

4.  Alternatively, you can use the following script to accomplish the same thing that was accomplished in the previous three steps. (Of course, you must substitute the name of your Database Maintenance Plan for Northwind Backup.)

declare @plan_name sysname
set @plan_name = 'Northwind Backup'  -- use your plan name here

select database_name
from sysdbmaintplans inner join sysdbmaintplan_databases
on sysdbmaintplans.plan_id = sysdbmaintplan_databases.plan_id
where plan_name = @plan_name

select name
from sysdbmaintplans inner join sysdbmaintplan_jobs
on sysdbmaintplans.plan_id = sysdbmaintplan_jobs.plan_id
inner join sysjobs
on sysdbmaintplan_jobs.job_id = sysjobs.job_id
where plan_name = @plan_name

Microsoft does not recommend querying system tables directly because system metadata table structures could change in a future release.

5. Once you know the names of your SQL Server Agent jobs, use the Enterprise Manager to general SQL scripts for the SQL Server Agent jobs. It’s a good idea to save each script to a separate file.

I do not recommend using Preview button and copying the script to the clipboard because control characters were picked up during my tests. Instead, save the script directly to a file as shown below:

6.      You’ll need to modify the SQL Server Agent scripts before executing them.

Creating the Database Maintenance Plan

Once the SQL Server Agent jobs for the Database Maintenance Plan have been created, only a few statements are required to create the plan:

declare @new_plan_id uniqueidentifier

exec msdb.dbo.sp_add_maintenance_plan 'Northwind Backup', 
@plan_id=@new_plan_id output

If you don’t have SQL Server Agent jobs for your plan, you are done. But if you do have jobs, they have to be added using the syntax that follows:

declare @new_command nvarchar(1000)

exec msdb.dbo.sp_add_maintenance_plan_job 
@new_plan_id,'YourNewJobIdGuid'

exec msdb.dbo.sp_add_maintenance_plan_job 
@new_plan_id,'YourOtherNewJobIdGuid'

As you can see, the problem is in adding the SQL Server Agent jobs to your Database Maintenance plan. You don’t want to have to get the job_id guides and manually update the calls to sp_add_maintenance_plan. Plus you need to modify the job creation scripts so they don’t use the hardcoded job_id guid that came from when the original Database Maintenance Plan was created. The modified job creation scripts will be incorporated into one master script as described in the next section.

Building the Script

If there was only one SQL Server Agent job, building the final SQL script would be trivial. When there is more than one job, combining the scripts for the individual jobs into a single script creates conflicts caused by nonunique variable and label names. When the SQL scripts to create the jobs were generated, each individual script contained the following:

DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
QuitWithRollback:
EndSave:     

The following changes have to be implemented to make a master script:

1. Declare the variables only once, which would be in the section of the script file that creates the first SQL Server Agent job. Comment out the variables in the subsequent sections where the job creation scripts are.
2. After the first job is created, @JobID must set to NULL before another job can be created.
3. Make the label names unique. Change QuitWithRollback to QuitWithRollback2 and EndSave to EndSave2 in the part of the script that creates the second job.

Additionally, the generated scripts for the jobs contain the plan_id guid of the original Database Maintenance Plan. You have to replace the hardcoded guid with @JobID.

Here is the outline for the completed script:

use msdb
declare @new_plan_id uniqueidentifier
declare @new_command nvarchar(1000)

exec msdb.dbo.sp_add_maintenance_plan 'Northwind 
Backup',@plan_id=@new_plan_id output

exec msdb.dbo.sp_add_maintenance_plan_db @new_plan_id,'Northwind'

in the script for the first SQL Server agent job,
edit the call to sp_add_jobstep to use @new_plan_id

insert script for first SQL Server Agent job

exec sp_add_maintenance_plan_job @new_plan_id,@JobID

set @JobID = null

in the script for the second SQL Server Agent job,
comment out DECLARE @JobID BINARY(16)
comment out DECLARE @ReturnCode INT
edit the call to sp_add_jobstep to use @new_plan_id
change QuitWithRollback to QuitWithRollback2
change EndSave to EndSave2

insert second SQL Server Agent job

exec sp_add_maintenance_plan_job @new_plan_id,@JobID

The completed Database Maintenance Plan creation script can be found here at maintenance.sql. Changes are clearly identified by block comments (i.e., comments using /*  */ syntax).

Conclusion

In about fifteen minutes, you can make a script to create Database Maintenance Plans. Anything that you do with a wizard can be scripted. Scripts offer the advantages of being repeatable and delivering consistent results. By delving into the system tables and stored procedures, wizard based tasks can be demystified. Once you understand what the wizard does, you can code it yourself. All it takes is diligence and patience.

--

John Paul Cook is a database and systems architect in Houston, Texas. His primary focus is helping large enterprise customers succeed with SQL Server, Oracle, and the .NET framework.