September 18, 2002 at 10:54 pm
Hi,
Came across a few articles on maintenance plans which are quite good. My only question is, if I used EM to create a maintenance plan, is there any way I can script this out. Atleast, EM does not give a straight forward option to do this.
Thanks & Regards,
Mitra
September 19, 2002 at 5:24 am
To the best of my knowledge you cannot script these.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 19, 2002 at 6:16 am
Agree. You could try to piece it together by finding the jobs related to the plan and scripting them, not quite the same as having a script that will repro the plan on another server.
Andy
September 19, 2002 at 7:32 am
Just in case you want to go ahead and try doing it for yourself
sysdbmaintplan_databases, sysdbmaintplans, sysdbmaintplan_history, and sysdbmaintplan_jobs
are the tables you need to take a look at, I may even try this myself if I ever get 5 minutes of my own time.
Nigel Moore
======================
September 19, 2002 at 8:12 am
Yes it might be true you can't build the script, although it is really easy to do it manually, and if I needed to move maintenance plans around it would also be easy to build a routine to do this. Maybe someday I will build this util. Here are the steps to moving a maintenance plan manually.
1) Run command on source server
execute sp_help_maintenance_plan
2) From the output determine which plan_id you wish to generate, then issue the following command on the source server replaceing <plan_id> with the plan you are thinking about generating:
execute sp_help_maintenance_plan '<plan_id>'
This command should identify the databases that the plan is associated with. Note the databases and SQL Server agent jobs associated with this plan.
3) Generate the script for the SQL Server agent jobs from the source server.
4) On the target server create the all the SQL Server agent jobs for the maintanenance job
5) Create the maintenance plan on the target server by issuing the following commands:
DECLARE @myplan_id UNIQUEIDENTIFIER
EXECUTE sp_add_maintenance_plan N'Myplan',@plan_id=@myplan_id OUTPUT
print @myplan_id
Note the plan_id that was created.
6) Is the following command on the target server, one for each database identified in step 2:
Execute sp_add_maintenance_plan_db <plan_id>',N'<database>'
7) Then execute the following on the target server, once for every job created.
EXECUTE sp_add_maintenance_plan_job N'<plan_id>', N'<job_id>'
Ok, I never said it was going to be easy.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply