How to change server name

Question

How to change server name

Niladri Kumar Saha

SSC Enthusiast

Points: 184
More actions
April 25, 2005 at 4:52 am

#109749

HI,
I want to change the server name in SQL Server.I am unable to do it.
Can anybody help me out??
==================================
As per Syed If I drop the server first and then add another server
Can I get all my databases with data in new database??
==================================
I am using Sql Server version 7 & 2000 but in different machine.
May I follow the same steps for SQL Server 7 also ??
Thanks & Regards
Niladri Saha

Thanks & Regards,
Niladri Kumar Saha

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

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

Syed-201559 SSC-Addicted Points: 477 More actions · Answer 1

You can rename the windows computer directly from My Computer Properties or system from control panel. You will be able to connect after restarting your machine.

To update sysservers system table use these two stored procedures.

sp_dropserver OldServerName

go

sp_addserver NewServerName, local

go

If you have more than one instance then still the process is same and you have to do it one by one for each instance.

sp_dropserver OldServerName\instancename

go

sp_addserver NewServerName\instancename, local

go

Dont forget to backup your master database after doing these changes.

Kindest Regards,

Syed
Sr. SQL Server DBA

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 2

Don't forget update the column originating_server of sysjobs with new server name in msdb.

sivaprasad SSCertifiable Points: 6183 More actions · Answer 3

Once you executed Sp_dropserver 'oldservername'

and sp_addserver 'newservername','local'

you can verify the server name by

Select @@servername.

Now the servername should reflect the new server name.

Otherwise stop and start the SQL Server service again and test the servername again.

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

Syed-201559 SSC-Addicted Points: 477 More actions · Answer 4

Renaming SQL Server does not effect any databases or data inside them. All your databases should be there after successful rename of computer.

Kindest Regards,

Syed
Sr. SQL Server DBA

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

Which version of SQL Server are you running? I believe everyone here assumes you are running SQL Server 2000.