Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Migrating Maintenance plans Expand / Collapse
Author
Message
Posted Wednesday, March 27, 2013 2:41 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:08 AM
Points: 383, Visits: 2,353
Hi,

Is it possible to migrate maintenance plans from 1 server to another server?

SueTons.


Regards,
SQLisAwe5oMe.
Post #1436163
Posted Wednesday, March 27, 2013 5:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:05 PM
Points: 20,485, Visits: 14,144
You can export them as SSIS packages and then import them back into msdb. However, they would then be SSIS packages.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1436225
Posted Wednesday, March 27, 2013 7:49 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:08 AM
Points: 383, Visits: 2,353
SQLRNNR (3/27/2013)
You can export them as SSIS packages and then import them back into msdb. However, they would then be SSIS packages.


So, you are saying that these plans won't be under Maintenance Plans?

SueTons.


Regards,
SQLisAwe5oMe.
Post #1436241
Posted Wednesday, March 27, 2013 9:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:05 PM
Points: 20,485, Visits: 14,144
They will not if you export as ssis packages.


Maint plans have a lot of shortcomings. This happens to be one of them. Many DBAs use script based maintenance routines for, in part, this reason.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1436249
Posted Thursday, March 28, 2013 12:34 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 4,379, Visits: 9,472
SQLRNNR (3/27/2013)
They will not if you export as ssis packages.


Maint plans have a lot of shortcomings. This happens to be one of them. Many DBAs use script based maintenance routines for, in part, this reason.


Sorry - this is not quite correct. When you export the to SSIS - the type of package is still defined as a maintenance plan. If you then open the package in BIDS, change the connection information (not the name) and perform a Save Copy As - you can place the package in the Maintenance Plan folder and it will still be a maintenance plan.

However, you have to also modify a setting in BIDS (which I cannot remember - since I haven't done it in a long time) to reset the GUID and other identifying information. If you do not make that change, the package runs - but will report failure even if all tasks are successful.

I find it is just easier to spend the time creating the plan - which doesn't take very long and is not something I do on a regular basis anyways.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1436661
Posted Thursday, March 28, 2013 1:15 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:05 PM
Points: 20,485, Visits: 14,144
Jeffrey Williams 3188 (3/28/2013)
SQLRNNR (3/27/2013)
They will not if you export as ssis packages.


Maint plans have a lot of shortcomings. This happens to be one of them. Many DBAs use script based maintenance routines for, in part, this reason.


Sorry - this is not quite correct. When you export the to SSIS - the type of package is still defined as a maintenance plan. If you then open the package in BIDS, change the connection information (not the name) and perform a Save Copy As - you can place the package in the Maintenance Plan folder and it will still be a maintenance plan.

However, you have to also modify a setting in BIDS (which I cannot remember - since I haven't done it in a long time) to reset the GUID and other identifying information. If you do not make that change, the package runs - but will report failure even if all tasks are successful.

Thanks for correcting that.

I find it is just easier to spend the time creating the plan - which doesn't take very long and is not something I do on a regular basis anyways.

+1




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1436685
Posted Thursday, March 28, 2013 2:16 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:08 AM
Points: 383, Visits: 2,353
Thanks guys. I found this article, see below.

This is actually doable, however the only issue is that, “The biggest issue is that maintenance plans have a hard-coded connection and if you just export/import – the plan on the new server would actually be setup to run on the source system.”

http://sqlbuzz.wordpress.com/2011/08/27/how-to-transfercopy-maintenance-plans-from-one-server-to-other/

SueTons.


Regards,
SQLisAwe5oMe.
Post #1436726
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse