How to: Rename a Stand-Alone Instance of SQL Server 2005

One of the questions I was asked at an event last week was about doing a side-by-side migration to a new SQL Server 2005 machine from an old SQL Server 2000 machine. 

 

Q: Can i rename the new machine to have the same name as the old machine once I remove the old machine from the network?

 

A: Yes you can. I cut-and-pasted the relevant section from the SQL Server 2005 Books Online.

 

When you change the name of the computer that is running Microsoft SQL Server 2005, the new name is recognized during SQL Server startup. You do not have to run Setup again to reset the computer name.

 

Important:  When a SQL Server instance is part of a SQL Server failover cluster, the process of renaming the virtual server differs from the process of renaming a stand-alone instance. For information on renaming a virtual server, see How to: Rename a SQL Server 2005 Virtual Server.

 

Important:  SQL Server does not support renaming servers involved in replication, except in the case of using log shipping with replication. The secondary server in log shipping can be renamed if the primary server is permanently lost. For more information, see Replication and Log Shipping.

 

You can connect to SQL Server using the new computer name after you have restarted the server. However, you should also update the server name change in the sysservers system table. Depending on whether you are updating a default or named instance, follow the appropriate procedure below.

 

To update the sysservers system table:

 

For a renamed default instance, run the following procedures:

sp_dropserver <old_name>

GO

sp_addserver <new_name>, local

GO

 

For a renamed named instance, run the following procedures:

sp_dropserver <old_name\instancename>

GO

sp_addserver <new_name\instancename>, local

GO