TN WW132 How to fix SQL after computer name is changed.
Description
Renaming a Computer that Hosts a Stand-Alone SQL Server Instance
- Author: Ernest Lee
- Published: 11/25/2015
- Applies to: SQL 2008 SP1 and higher
Details
You can connect to SQL Server by using the new computer name after you have restarted SQL Server. To ensure that @@SERVERNAME returns the updated name of the local server instance, you should complete either of the following procedures. The procedure you use depends on whether you are updating a computer that hosts a default or named instance of SQL Server.
Figure 1 (below) shows that the Host name is correct, but running SELECT @@SERVERNAME AS 'Server Name' returns the old server name. The New Server Name should return VMK6432New.
To rename a computer that hosts a stand-alone instance of SQL Server
Run the following Stored Procedures:
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
To rename a computer that hosts a named instance of SQL Server,
Run the following Stored Procedures:
sp_dropserver <'old_name\instancename'>
GO
sp_addserver <'new_name\instancename'>, local
GO
Example
To rename a computer that hosts a default instance of SQL Server
Run the following Stored Procedures (Figure 2 below).
sp_dropserver VMK6432New
GO
sp_addserver VMK6432New, local
GO
Restart the SQL Server (MSSQLSERVER) service (Figure 3 below).
As a result of the Host running SELECT @@SERVERNAME AS 'Server Name' statement the current server name is returned (Figure 4 below).
