How to change server name in Jobs?

  • Hi experts,

    After we replaced our old server, we changed the server name from AMKDW to SVDW.

    Many of the jobs are failed and I beleive it's because the server name is changed and the source of the jobs are still set to AMKDW.

    How to change the source server to the new one?

    Thank you.

  • I didn't believe there was any reference to the source server in a job?

    I have tested by scripting out a job from one of our dev servers and the only reference to the source server is in the comment at the top of the script?

    Did you restore the msdb from a backup from the "old" server when you moved to the "new" server?

    Regards

     

    Carl

     

     

     

     

  • If select @@servername returns the wrong servername then issue:

    EXEC sp_dropserver 'WrongServerName'

    EXEC sp_addserver 'CorrectServerName', 'local'

     

    You may also need to change the originating server in sysjobs otherwise the sql agent will think there is a master job server and will not let you modify a job

    use msdb

    go

    update dbo.sysjobs set originating_server = 'CorrectServerName'

  • Well you learn something new every day. 

     

    I wasn't aware of the originating_server column in sysjobs.

     

    thanks Andrew

     

    Regards

     

    Carl

  • Thank you Andrew.  It does work after I set originating_server to new server name and I checked the properties of all jobs, the source server did changed to the correct server.  However, this morning, many other jobs are failed and when I check the properties, they are not allowed to view it anymore.  The message states "The job 'xxxxxxxxxx" does not exit on the server" on all those failed jobs.

    since I am not able to view the properties, I don't know what is going on, is there anyway to find out what happened?

    Thank you again.

  • Jennifer,

    Could you not script out one of the failed jobs and run the script to drop and create it and then see whether the job works after doing this?

    The only down-side I can see with this is that you will lose the history of the job.

    Regards

    Carl

     

  • Thank you Carl.

    I don't mind to loss job history, so I run the script again and re-created the jobs.  Now they are running perfectly.

     

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

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