How to transfer Maint Plans (chkdb,reorg & so on) from one server to other

  • BIDS Tx. Jobs task doesn't work as maint plans don't get tx. to the target MSDB db. I have had issues in importing packages to the target server, Is there any other way, I have got 4 servers where I need to tx. them to, & thought if there is a easier way.

  • Any solution or trick for this?

    MJ

  • Hi,

    If you have installed Integration Services on the server, you can connect to the instance and export them and the import them in the other server.

    Regards.

    Jose Santiago Oyervides.

  • Hi Jose,

    http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/567ca99e-3627-4787-b54b-413ba5ce278c

    Link for the issues I am having with IS export.

  • Here is the process I use:

    1) Create maintenance plan in SSMS as template. Create all sub-plans, tasks, etc...

    2) Open BIDS and create new SSIS package

    3) Add Existing Items to new package - import from SQL Server where I created the SSMS template.

    4) Once the packages have been imported into BIDS - then we can modify certain aspects of the plans. Note - making further changes can cause issues.

    5) Change Local Connection to point to target server (DO NOT CHANGE THE NAME - only change the properties to the point to the target server).

    6) Modify each task that needs to be modified - for example, backup task - modify destination folder if needed, cleanup task - modify destination folder, etc...). Note: do not try to add tasks or remove tasks, this can be problematic.

    7) Save Copy As - select target server, specify maintenance plan folder and give it a name. If there is already a plan with that name you need to delete the previous plan first.

    This works as long as you are saving the plans to a SP2 or greater instance (actually, the minimum should be 9.0.3054, anything less than that will cause problems).

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • I can try the above process next time, step 3 is still the issue tho, can't export pkg coz of the error, anyone knows what the error is about, for now I have manually create maint plans on all servers.

  • I will try this next time, think will work:

    1. Use GUI or dtutil to copy all packages from one server to other.

    dtutil /COPY DTS;"msdb\maintenance plans\testdtutil" /DTS "msdb\maintenance plans\testdtutil" /SOURCESERVER server1 /DESTSERVER server2

    2. Use BIDS tx. jobs utility to tx. the jobs.

    3. Check & modify jobs on destination as necessary.

  • I tried the above, tx. maint plans & tx. jobs, Modified the jobs - gave error while executing, Modified maint plans - gave error, better just to create new maint plans.

Viewing 8 posts - 1 through 8 (of 8 total)

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