Thursday, May 7, 2009

How to change the name of an SQL Server machine

You may experience problem with replication and linked servers and none of SQL Agent jobs are going to work properly after rename of the server.

Here is the script that we can run on the SQL server 2000 after server get renamed in XP/2000/NT level:

sp_helpserver --to see the list of servers

-- probably only yours and probably the old name?

exec sp_dropserver 'OLD SERVER NAME'


exec sp_addserver 'NEW SERVER NAME', local Go

-- In SQL Server 2000 do the following!!!

USE msdb


Update msdb..sysjobs

set originating_server = 'NEW SERVER NAME'

where originating_server = 'OLD SERVER NAME'


-- Then restart SQL server services. You're done for 2000

No comments:

Post a Comment