November 5, 2002 at 12:07 pm
I have a system that needs to be distributed to multiple servers. There are multiple DBs involved.
How can I script the maintenance plans, and the schedule ? I can use one server as the model, but what comands will dump the plan and schedule so I can reapply to other servers ??
Thanks
KlK, MCSE
KlK
November 5, 2002 at 12:43 pm
You should be able to find this information in MSDB Database.The tables of your interest might be sysdbmaintplans,sysdbmaintplans_Jobs,sysdbmaintplans_History,sysdbmaintplans_Databases,SysJobs,sysjobschedules Etc.
November 5, 2002 at 1:31 pm
Actually I know where to locate it, I want to script it to export it to another DB, and to a script directory for backup.
KlK, MCSE
KlK
November 5, 2002 at 1:54 pm
There is no simple process of moving maintenance plans. Having said that 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 replacing <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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply