Migrating Maintenance plans

  • Hi,

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

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • 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[/url]
    Learn Extended Events

  • 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.

  • 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[/url]
    Learn Extended Events

  • 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
    “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

  • 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[/url]
    Learn Extended Events

  • 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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply