Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 20, 2003

Migrating a Maintenance Plan from One SQL Server to Another - Page 2

By Gregory A. Larsen

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
print @myplan_id

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 Maintenance Plan

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 maintenance plan.

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 so:

exec msdb.dbo.sp_help_job  
     @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:

exec msdb.dbo.sp_add_maintenance_plan_job 
       'E219D922-9B4F-4C15-BC16-35E0C93CB3EA',
       '81160E1C-9AA8-4104-9A0A-5463534C08EC'


Conclusion

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date