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).