Post reply

migrate maintenance plans from one server to another

  • June 24, 2009 at 6:19 am

    #155872

    Hi all,

    I have been reading for a while on how to transfer maintenance plans created on one sql server 2008 to another server sql server 2008 residing on a different machine. I have not found an easy way to do this.

    I was wondering if anyone can give me a hint on how to deal with this.

    Thanks indeed for your help

    Regards

    Niyala

  • June 24, 2009 at 7:02 am

    #1014621

    Yes you can copy/Export Maintenance plans across SQL Servers.

    Maintenance Plans are SSIS Packages.

    Connect to Server->Integration Services.

    goto MSDB-> under MSDB there should be a folder named Maintenance plans.

    Expand the Folder.. and click any of the plans and you can export it another server or store it as a file. etc....

    Let me know if this helps...

    Maninder
    www.dbanation.com

  • June 24, 2009 at 7:19 am

    #1014629

    Thanks for your help. The maintenance plan did transfer and somehow the sql Agent jobs too. However, I start a job, it failed. I am not aware of what to change or edit so that the jobs work on the destination server. The two servers have the same directory structure and both are named instances with the same name.

    How to go further?

    I appreciate

    Niyala

  • June 24, 2009 at 7:25 am

    #1014633

    Named instances with the same instance name but different servernames.. correct?

    Check the job properties and check the owner.. give a valid account.. like a SA or the saem account the SQL Agent service is using...

    Maninder
    www.dbanation.com

  • June 24, 2009 at 7:32 am

    #1014637

    Hi,

    The job owners are the same in fact. I found out that in the destination server, the job schedules were not exported. All schedules of all jobs on the destination server are empty.

    How to work around?

    Regards

    Niyala

  • June 24, 2009 at 7:35 am

    #1014639

    But, when you right click and start the job, does it work or throws error..

    Maninder
    www.dbanation.com

  • June 24, 2009 at 7:43 am

    #1014646

    It does start but fails to succeed.

  • June 24, 2009 at 7:50 am

    #1014652

    will you be able to post the error message..

    Maninder
    www.dbanation.com

  • June 24, 2009 at 8:09 am

    #1014667

    Thanks, but the DB server is off-line and hence difficult to send out information from there. What I see, however, are the following error codes:

    0XC00291EC

    0X80019002

    BUT if the schedules were not exported to the destination server, there must have been already a problem during transfer??

    Regards

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

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