SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Migrating Maintenance plans


Migrating Maintenance plans

Author
Message
SQLisAwE5OmE
SQLisAwE5OmE
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 3068
Hi,

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

SueTons.

Regards,
SQLisAwe5oMe.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31946 Visits: 18551
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SQLisAwE5OmE
SQLisAwE5OmE
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 3068
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.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31946 Visits: 18551
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7639 Visits: 9966
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31946 Visits: 18551
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SQLisAwE5OmE
SQLisAwE5OmE
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 3068
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search