February 6, 2008 at 1:16 pm
Hi
How can I generate script from Database Maintenance plan? My scenario is to add ~ 5 servers every month (small server) so I can run the script instead of create new Maintenance plan for each server.
Note:1) I can use the image but I really need to use script if I can.
2) Relate jobs show link back to Maintenance plan, so script only job will not work.
Thank you in advance.
February 6, 2008 at 2:31 pm
How about this one?
SELECT * FROM sysjobs sj, sysdbmaintplan_jobs sdj, sysdbmaintplans sdp WHERE sj.job_id = sdj.job_id AND sdj.plan_id = sdp.plan_id AND plan_name = 'DB Maintenance Plan1'
February 6, 2008 at 2:33 pm
As far as I know, there isn't a way to script out a maintenance plan. They're SSIS packages, so you might be able to copy the package to a new server and change connections and such then create a job, which you can script out, to run it. I've never tried it, though.
Greg
February 6, 2008 at 2:50 pm
This isn't a script but here's another way to get the maintenance plan on other servers.
From Object Explorer connect to Integration Services. Expand Stored Packages/MSDB/Maintenance Plans. Right click on a plan and select Export Package. You can go directly to the other server or to a file which you could then import on the other server.
The Jobs would still have to be scripted.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply