January 22, 2008 at 8:54 am
Does anyone know of the easiest way to export Maintenance Plans between servers?
January 22, 2008 at 9:09 am
I suppose you could export the data from the msdb tables (sysdbmaintplans), but the problem is these plans have so much stuff connected to your server. They're not as generic as you might think.
You could open the plan in BIDS and then you can deploy it to any number of servers, making changes as needed. That might be the easiest way. However I've had issues opening plans in the maintenance plan designer after this. You might be stuck using BIDS forever with the plans. Not the worst thing, but you need to know this.
Could script something by exporting with SMO, but then you're building an app that might not be worth the time invested in it.
January 22, 2008 at 9:12 am
January 22, 2008 at 1:51 pm
Hi Steve,
If you are referring to SQL 2005 then try using SSIS to move plans between servers & instances. There are a few doc's on the web that will take you through step by step .
Cheers,
Mark
January 23, 2008 at 2:02 am
Thanks for the replies. I'll go ahead and try.
With regard to SSIS, I have tried exporting plans but could not see how to do this via the msdb db, that some users have hinted at using?
January 23, 2008 at 4:30 am
Steve,
Ok, if I connect to integration services on my specified sql server where my maintenance plans reside and then expand 'Stored Packages', 'MSDB' & 'Maintenance Plans' I can see a list of plans that have been created. If I right-click on one of those plans I get the option to 'Export Package' which then allows me to select the Server, Authentication Type & Package Path.
With these settings chosen I can transfer single plans to any other SQL 2005 Server or Instance. I'm not sure if you can transfer multiple plans in one go. This would be really handy for us as we have numerous servers with up to eight named instances per server.
Hope this helps,
Mark
January 23, 2008 at 4:38 am
Steve,
If your working with named instances then this article will also be useful
http://msdn2.microsoft.com/en-us/library/ms137789.aspx
Regards,
Mark
January 24, 2008 at 5:20 am
Mark
Apologies but where do I expand 'Stored Packages', 'MSDB' & 'Maintenance Plans' from? I have created a new SSIS Package and created a Connection Manager to my Server where the Maint plans reside but then I can't find the above option???
Thanks
Steve
January 24, 2008 at 5:59 am
Hi Steve,
From within SSMS connect to Integration Services and login. Once you've logged in you should see 'Stored Packages' under Object Explorer which you can then expand and then drill down to 'Maintenance Plans'.
Cheers,
Mark
January 24, 2008 at 6:06 am
Mark (1/23/2008)
Steve,Ok, if I connect to integration services on my specified sql server where my maintenance plans reside and then expand 'Stored Packages', 'MSDB' & 'Maintenance Plans' I can see a list of plans that have been created. If I right-click on one of those plans I get the option to 'Export Package' which then allows me to select the Server, Authentication Type & Package Path.
Mark
MArk, just a warning, I had a need to duplicate a maintenance plan on another server so I tried this but unforunately hit problems. scheduling information was lost on the source and destination server. Also by the time you go through and change the connection strings almost as much work as starting from scratch. As steve Jones warned I did have problems editing the plans and was unable to delete the plan thru SSMS, threw an error. Had to delete via SSIS. ended up losing plan on both servers!
Nice idea though!
SQL 2005 version 9.00.3200
---------------------------------------------------------------------
January 24, 2008 at 6:44 am
Steve,
Perhaps I should have mentioned this but be aware of some of the problems you could face as mentioned by Steve and more recently George in earlier posts.
Although i've used this method on a few occaisions to 'drop' plans to another server I too have come across some strange results. It may be just as quick to recreate the plans on the server/s they need to be on. That said it does work just make sure you scrutinise the plans after they've been exported and correct any problems you find.
If possible test it thoroughly within a sql 2005 test environment.
Mark
January 24, 2008 at 6:52 am
Thanks Mark - I'm with you now.
Have imported a plan and see what you mean with having to change the server name etc etc. At least it keeps the steps and step names!
Regards
Steve
January 24, 2008 at 9:35 am
I've also found that the .txt log files are not created either. Would be quicker to create jobs from scratch!
June 23, 2008 at 9:46 am
I tried exporting a Maintenance Plan to a test server so I could play around with it -- well once I renamed it on the test server, it renamed on the Production server! That scared me, because I had already modified the subplans. Fortunately, modifying the subplans did not take effect on the Production server. But it's still not something I would recommend fooling with.
Better to just create from scratch. (easiest to open the source maint.plan and the new maint.plan in side-by-side windows and do it that way)
June 23, 2008 at 12:00 pm
I have also found the Maintenance Plans difficult to deploy to multiple servers. I think that here T-SQL stored procedures and jobs are easier to manage than Maintenance Plans. (Just script the objects and jobs and create on another server.)
Ola Hallengren
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply