Post reply

MSDB database??

  • August 9, 2006 at 5:41 am

    #114075

    Hello.

    We are moving to a new platform but backend technology remains same. Just that we are moving from SQL Server 2000 to SQL Server 2000 with Clustering.

    I have a question. Maintenance plans and jobs are on msdb database. I was thinking of backing it up and restoring on new server. Are there any implications of doing so? I read we have to stop the SQL Server agent before  doing so and not click on MDTS. Anything else that I should be doing?

    Looking forward for some advice.

    Thanks in advance.

    Regards,

    Madiha.

  • August 9, 2006 at 6:37 am

    #654040

    Hi Madiha,

    Restoring msdb is fairly straight forward and you should only need to worry about stopping SQL Server Agent.  Nothing bad will happen if it's running - it just won't let you restore msdb.

    You do have to remember though that you should only restore msdb on a server if the server names are the same.  i.e. you shouldn't restore msdb from serverA onto serverB - it is possible but you have a few more things to worry about.

    Hope that helps,

    Karl
    source control for SQL Server

  • August 9, 2006 at 10:03 am

    #654124

    Here comes the problem. We have different server names

    Any hints on how we should proceed.

    Please do reply.

    Regards,

    Madiha.

  • August 9, 2006 at 6:51 pm

    #654195

    Madiha,

    All you need to is probably the following:

    update msdb..sysjobs set originating_server = '<new server>' -- your new server name

    where originating_server = '<old server>' -- your old server name

    Just test all jobs after your msdb restore and see whether there is any error popping up.

    HTH,

    jy

  • August 10, 2006 at 2:19 am

    #654218

    Yep,

    That is what I was going to suggest but I'm always a bit reluctant to advise other people to update system tables.  I'm happy to do it myself but it's a different matter advising someone else to do it.

    Karl
    source control for SQL Server

  • August 10, 2006 at 3:46 am

    #654241

    Thanks a lot everyone. I will try this and if there's a problem, you would see me again. Just that its a prod system, so I am a bit shaky.

     

    Thanks a lot,

    Madiha.

  • August 10, 2006 at 5:22 am

    #654261

    Got a problem When I tried to restore, it terminated as it said that backup can not be restored as it was taken by a different version of server than this one.

    My local server is a desktop version; maybe thats the cause. But when I restore other databases, I never get an error then why with msdb one. I wanted to test before I did it on prod server. I can try on staging and dev servers but I don't want to mess up right as there is heavy dev going on. But I'd have to go with it I guess.

    Isn't there a  way to copy jobs across without restoring database.

  • August 10, 2006 at 6:23 am

    #654274

    Hi,

    Unfortunately, you cannot restore system databases to a server with a different version to the one from which the backup was taken.

    You could just script the jobs and apply the script on the new server - that's easy enough.  Problem is that you cannot easily script database maintenance plans.

    Karl
    source control for SQL Server

  • August 10, 2006 at 7:13 am

    #654293

    Thats fair enough. I am not interested in scripting maintenance plans.

    I will look into it.

    This forum is a real help. Thanks folks.

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

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