Generate Maintenance plan create script from exists maintenance plan.


I have maintenance plan that include sub-plan and job is associated with corresponding maintenance plan.

I am looking any option to generate maintenance plan create script from existing maintenance so I could change required database information and name and use that script for creating another maintenance plan with different database information.

Will be good if that script contain related job creation and schedule information.
more ▼

asked Apr 23, 2012 at 12:54 PM in Default

Amardeep gravatar image

1.3k 87 88 89

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

The maintenance plans are stored in MSDB database, so to get this maintenance plan you should connect to SSIS in management studio, Expand MSDB, Expand Maintenance plans; This will list out the Maintenance plans present in the server. You can export this to your file system by right clicking it, Export Package, Choose Package Location to file system, and specify a location to save the maintenance plan as dtsx file to the disk. Later you can edit this using BIDS or Visual studio for any changes, Save the dtsx package and Exeport to SSIS.

more ▼

answered Apr 23, 2012 at 01:12 PM

Cyborg gravatar image

10.6k 36 40 45

(comments are locked)
10|1200 characters needed characters left
Maintenance plans are not scripts. They are SQL Server Integration Services packages. You can't modify them without using either Management Studio or Database Tools (Business Intelligence Database Studio, BIDS for those on pre-2012).
more ▼

answered Apr 23, 2012 at 01:09 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 23, 2012 at 12:54 PM

Seen: 3122 times

Last Updated: Apr 23, 2012 at 01:12 PM