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.
SQL Server 2008 – Modify Maintenance Plan Using T-SQL Script
maintenance-planssql serversql-server-2008t-sql
Related Question
- SQL Server – How to Script a Maintenance Plan
- Sql-server – How to know that , Is it Maintenance Cleanup Task is working properly from maintenance Plan or not through T-SQL
- SQL Server 2012 – Deleting Other Files During Maintenance Plan
- SQL Server – How to Modify Backup Directory Path in Ola Hallengren Maintenance Script
- Sql-server – How to share values in maintenance plan tasks
- Sql-server – TSQL – SQL Server 2008 Maintenance Plan – anything else
- Ola’s Maintenance Solution Plan script newer version
Best Answer
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