Data Management

How do I change the name of my SQL Server?

Did you know that when you change the name of a server that you also have to change the name in SQL Server? It's not that difficult, but if you don't you'll cause problems. Steven S. Warren shows you how.

 

 

Have you ever had to change the name of a server that has SQL Server installed on it? If you anwered yes, did you know that you also have to change the name in SQL Server? Here are the steps to perform the change.

1. Open SQL Server Management Studio and click New Query.

2. Type Select @@ServerName to verify that the server name is correct or incorrect. In this example, I changed the Windows 2003 from WIN2K3R2EE to SQL Server. I did not change the name in SQL Server 2005. It will still return the old name. Let's fix it.

3. Next, type sp_dropserver 'WIN2K3R2EE'

4. You are now ready to add the correct name by typing sp_addserver 'SQLSERVER'. local

5. Restart sql server and the sql server agent by opening a command prompt and typing net stop mssqlserver and net start msssqlserver. To stop and start the sql server agent, type net stop or start sqlserveragent.

6. Click New Query in SQL Server Management Studio and type select @@servername to verify everything is correct and you have successfully changed the sql server name.

13 comments
mbaraz
mbaraz

This sounds like what is ailing me. This is a production server I need to change. What happens to all the database objects (users, databases, etc.) that are running? Will we lose anything or will they everything be there under the new server name after the drop and add of the server meaning it is just the name getting manipulated?

VORMEG1961
VORMEG1961

Does this also function, if the server takes part in a replication process?

cookspc
cookspc

What if you have a server that is not part of a domain and you join it to a domain. Does that change the server name as far as SQL Server is concerned? I have a Windows 2003 server running SQL Server 2005 that is not part of my domain.

richard.wood.15.ctr
richard.wood.15.ctr

This post could not have happened at a better time! We were troubleshooting a connection problem between Perception 4.3 and our SQL server. We had renamed the SQL server but neglected to change it in SQL. Thanks for a great post and making hours of research into minutes of work!

rbarrera
rbarrera

I wrote a script to automatically do this: ------------START OF BATCH FILE------------ REM 32 OR 64 BIT CHECK FOR /F "TOKENS=3 DELIMS= " %%A IN ('SYSTEMINFO^|FINDSTR /I /C:"SYSTEM TYPE:"') DO (SET BITTYPE=%%A) FOR /F "TOKENS=1 DELIMS=-" %%A IN ("%BITTYPE%") DO (SET BITTYPE2=%%A) @ECHO OFF IF /I '%BITTYPE2%'=='X86' SET PF=%PROGRAMFILES% IF /I '%BITTYPE2%'=='X64' SET PF=%PROGRAMFILES(X86)% REM THIS WILL UPDATE THE INSTANCE NAME ON A CLIENT, WHERE THE PC NAME HAS BEEN CHANGED AFTER THE INSTALLATION OF SQL REM YOU SHOULD NOT BE USING DELIMINATORS (- SIGN) IN YOUR PC NAMES!!!! USE (_ UNDERSCORE) INSTEAD, OTHERWISE THIS SCRIPT WILL NOT WORK %SYSTEMDRIVE% SET "%PF%\Microsoft SQL Server\90\Tools\Binn\SQLCMD" -S "127.0.0.1" -Q "EXEC sp_dropserver @server = @@servername" "%PF%\Microsoft SQL Server\90\Tools\Binn\SQLCMD" -S "127.0.0.1" -Q "EXEC sp_addserver %COMPUTERNAME%, 'local'" REM RESTARTING SERVICES FOR SQL SERVERNAME TO TAKE EFFECT NET STOP MSSQL$(YourInstance) /y NET STOP SQLSERVERAGENT /y NET STOP MSSQLSERVER /y NET START MSSQL$(YourInstance) NET START MSSQLSERVER NET START SQLSERVERAGENT ------------END OF BATCH FILE------------ If you are using an instance, just add it to the script.

roderick.dusek
roderick.dusek

Do client ODBC connections to the SQL server actually map to the server name or the SQL server name? Do the server name and the SQL server name have to be the same? RD

pirho
pirho

Thats a great tip to know, but what about SQL 2000, I have this problem where the server name needs to be changed because a naming convention policy mandated by our head office.

Tony Hopkinson
Tony Hopkinson

It would be nice if MS did a startup check and logged it here and there....

Tony Hopkinson
Tony Hopkinson

is dependant on the ODBC driver, for SQl Server it is the sql server name. If the names are different, you run into problems. Any piece of code that uses @@servername to do something with the server that the sql sever is on will go wrong in some way or other. The error messages you get at this point can be seriously confusing to say the least.

ozchorlton
ozchorlton

Might also be good, if the author checked that the last picture was correct?

joey
joey

What about the user groups like SQLServer2005MSFTEUser, SQLServer2005MSSQLUser, and SQLServer2005SQLAgentUser that are used to assign user roles. They begin with the servername. Changing the server name doesn't change these accounts. Do they need to have the same name or does the SID take care of that?

RPip
RPip

I've used the same procedure on SQL2000. Just don't forget the 'local' when you add the new name!

Tony Hopkinson
Tony Hopkinson

the names as data, they don't matter. SID is the identifier To be neat and tidy you could rename them as well, especially of you were doing a lot of renaming and didn't want to tax the brain with what the server name used to be.