How to change server name in Jobs?

Question

How to change server name in Jobs?

jenniferhu46

SSCrazy

Points: 2990
More actions
November 28, 2005 at 9:38 am

#110944

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.

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

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

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 1

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

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 2

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'

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 3

Well you learn something new every day.

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

thanks Andrew

Regards

Carl

jenniferhu46 SSCrazy Points: 2990 More actions · Answer 4

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.

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 5

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

jenniferhu46 SSCrazy Points: 2990 More actions · Answer 6

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.