May 21, 2010 at 8:52 am
Hi folks,
I wonder if it is possible to script a maintenance plan as well as its schedule into script?
Thanks.
May 21, 2010 at 12:18 pm
No - you can't script the maintenance plans, at least I am not aware of any way to do this.
With that said, there is a way you can export/import the maintenance plans - but there are additional requirements to be able to import the maintenance plan into a new system. The biggest hurdle is resetting the GUID appropriately for the plan.
If you want to do something like this manually, you can create a new SSIS package in BIDS. Import the maintenance plans from the server where you built them. Change the connection information (do not change the name - it will break the plan) to the new server. Modify the tasks and update the specific information in each task (e.g. databases and backup paths).
Make sure you do not add any tasks or remove any tasks, or try to change the plans - this will cause problems when you try to run the imported plans.
Once you have made those changes, select File | Save Copy As and copy the plan to the new server and put it in the maintenance plan folder.
Log into SSMS on the new server, open the maintenance plans and edit each plan to create the schedule. Save the plan and you are all set.
Note: the owner of the plan will be your account - if you (like me) don't want to be the owner, then update the owner SID in the table msdb.dbo.sysdtspackages90 for your plans (packagetype = 6). I make 'sa' the owner of the plan. You have to edit each plan and save it again to update the job owner.
Good luck...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 21, 2010 at 12:52 pm
I believe the technical term for the above is what we call a 'right palaver' 🙂
I wonder if microsoft realised the effect on DR procedures when they disassociated SSIS from the database engine as compared to DTS.
I really miss being able to restore msdb and just update the originating_server column
---------------------------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply