Renaming an Instance of SQL Server
This chapter explains how to rename an instance of SQL Server, which is necessary if the name of the host machine changes.
Key Concepts
An instance of SQL Server takes its name—either fully or in part—from the server that it is installed on. If the host machine is renamed, the SQL Server instance also needs to be renamed. If it is not renamed, SQL Server will still function, but there are certain places that will have errors because the names are not in sync.
This section explains how to rename the SQL Server instance after you have renamed the physical server. It includes the following subsections:
To rename the SQL Server instance after you have renamed the host machine, you run a series of queries against the JICS database, as described below.
To rename the SQL Server instance:
Log in the server hosting the JICS database and start SQL Server Management Studio.
Determine the name of the server instance by running the following query:
SELECT @@servername
In most cases, the system returns the current name of the SQL Server instance, which is based on the former name of the physical server. However, in some cases, the system may return NULL. For details on why this occurs and how to determine the SQL Server instance name in this case, see When I run @@servername, the system returns NULL. What does that mean? How do I fix it?.
Eliminate the current name by running the following query:
EXEC sp_dropserver '<the current SQL Server instance name>' GO
Assign a new name to the SQL Server instance by running the following query:
EXEC sp_addserver '<the new SQL Server instance name>', 'local' GO
Restart the SQL services.
Check to see if SQL Server recognizes the new name by running the following query:
SELECT @@servername
In most cases, the system returns the new name. In some cases, the system returns NULL. For details on resolving this, see When I run @@servername, the system returns NULL. What does that mean? How do I fix it?.
If you have followed the steps to rename the server instance, then this means that SQL Server either does not have a record for the existing name or it hasn’t recognized the name. To check, use the steps below.
Run the following query in Management Studio:
SELECT * FROM master..sysservers
Do one of the following:
If there is a record returned where srvid = 0 and srvname shows a name, this means the name exists but is not being recognized for some reason. Assuming the name is correct and you want it to be recognized (that is, that it reflects the new name of your server hardware), you may need to restart the SQL Server services again or reboot the server. By contrast, if you are still at step 1 of Rename the SQL Server Instance and this is not the correct name, simply make a note of the old name for the next step in the procedure.
If you have already followed the steps to rename the server instance, and you see a record for the correct srvname, but srvid is not 0, you should:
Run the sp_dropserver procedure for the correct name.
Run sp_addserver adding the ‘local’ parameter.
When I run @@servername, the system isn’t showing the correct name. What do I do?
If you completed the steps to rename the SQL server, restarted the SQL Server services, and the system isn't showing the correct name, then run some of the same checks mentioned in “Question: When I run @@servername, the system returns NULL. What does that mean?...”. If everything looks as it should, you should try stopping and restarting the services again or rebooting the server.
There are several SQL Server services in the Control Panel. Which services should be restarted?
If you want to make sure that all of the SQL Server services currently running are running after the restart, just restart the SQL Server (MSSQLSERVER) services. This should stop and restart all the related services.
Alternatively, you can stop and then start the SQL Server services. In this case the system will stop all the related services, but you will need to manually start the ones that were stopped.