How can I modify a maintenance plan in SQL Server 2008 using a T-SQL script?

I want to change the schedule of a maintenance plan and change the path of the .bak file .

All these changes should be done using a T-SQL script.


1 Answer 1


There are 2 parts to this question, I'll treat them one by one.

Modifying the schedule

Modifying the schedule isn't really changing the maintenance plan as much as changing the SQL Agent schedule.

Have a look at sp_update_schedule for this need.

Modifying the Maintenance plan itself

This will be hard using pure T-SQL as the maintenance plan is stored as an SSIS package. You will have to resort to CLR integration for this.

If you want to do that you would have to export the package from the SSIS package store. (see here) and then modify them through c#.

See Building Packages Programmatically and Modifying an SSIS Package through code

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.