Renaming a Computer with SQL Server Instance

After SQL Server installation internal SQL Server is based on the host computer name. However, when you are changing the host name, old SQL server name in the metadata (stored in sys.servers ) is not updating automatically. But you don‘t need to reinstall it completely to change the name of SQL server! In this step-by-step tutorial we will show you how to change server name in a correct way with a standalone non-clustered instance of Microsoft SQL Server. This manual is applicable for Microsoft SQL Server from 2000 to 2012 versions.

Tip. You can‘t change the SQL Server Instance name without a complete reinstalling.

Now let‘s start. Open SQL Server Management Studio and check the current host name and SQL Instance name:

Select @@ServerName ServerName, Host_name() HostName

Now you can see:

• Current Server Name: DESKTOP-V20E3PO
• Current SQL Instance Name: DESKTOP-V20E3PO\SQLDB

Let’s rename the host and restart server by running command:

Rename-Computer -NewName Sql-Srv1 -Restart

Tip. You can change the name of host from My Computer properties also.

After rebooting, try to establish connection using old SQL server name — DESKTOP-V20E3PO\SQLDB. You will get an error, because the server name was changed before.

A network-related or Instant-specific error occurred when you was trying to establish connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and SQL Server is configured to allow remote connections. (provider: SQL Network Interface, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server)

Re-check Instance name and hostname:

Select @@ServerName ServerName, Host_name() HostName

In our case, it is clear that SQL instance name does not match the name of computer and still referring to old host name DESKTOP-V20E3PO.

To change hostname for named SQL Instance run following TSQL commands:

Drop old server name from server list:

sp_dropserver "DESKTOP-V20E3PO\SQLDB";
go

Add new server name as default:

sp_addserver "Sql-Srv1\SQLDB", local;
go

To Rename Host of a SQL Server default instance run the following:

sp_dropserver ″old_name″;
go
sp_addserver ″new_name″, local;
go

Additional steps

You can get an error while executing sp_dropserver stored procedure:

Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56

There are still remote logins or linked logins left for the server DESKTOP-V20E3PO.

You have to drop the remote logins first.

For Default instance:

sp_dropremotelogin DESKTOP-V20E3PO;
go

For Named instance:

sp_dropremotelogin DESKTOP-V20E3PO\SQLDB;
go

These commands should be executed for each SQL instance on the machine.

Verify that server name in the metadata (sys.servers) was updated:

SELECT @@SERVERNAME AS ‘ServerName’

SELECT * FROM SYS.SERVERS

Rename was successful!

The only thing you need to do is to restart SQL Service:

net stop MSSQL$SQLDB && net start MSSQL$SQLDB

Important:

• You can rename computer name in such way only with Standalone SQL server. To rename SQL Server under Failover Cluster use Cluster Management snap-in.
• You cannot rename a server involved in replication or database mirroring.
• If you have installed Reporting services, after renaming it may not be available. Find out more here.