question

Sharma avatar image
Sharma asked

Generate Maintenance plan create script from exists maintenance plan.

Hi, 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.
sql-server-2008jobmaintenance-plans
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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).
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.