How to Rename SQL Server Stand Alone Instance After Installing SQL Server

Introduction

In this article we will take a look at the steps you need to follow to rename a stand-alone instance of SQL Server. The steps mentioned in this article are applicable for SQL Server 2000 and higher versions.

Get Local SQL Server Name

Execute @@SERVERNAME built-in configuration function to return the name of local server that is running SQL Server.

SELECT @@SERVERNAME AS 'Server Name'
GO

Rename SQL Server Stand Alone Instance or Default Instance of SQL Server

In case if you are using a named instance of SQL Server, then you can just rename the host name and not the SQL Server Instance Name. For example, let us assume that you have a named instance of SQL Server as SERVERONE\SQL. Using the below script you can just rename the host computer name from SERVERONE to NEWSERVERONE. You cannot rename the instance name SERVERONE\SQL to NEWSERVERONE\NEWSQL. In case if you want to rename SQL Server Instance name then the only way out is to Uninstall SQL Server and Install SQL Server with a new Instance name.
Execute the below TSQL script for a renamed computer which hosts a default instance of SQL Server.

Use Master
GO
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO

Important Note: - Once you have executed the above TSQL script, go ahead and restart SQL Server Instance Service using SQL Server Configuration Manager.

---

Rename Host Name of Named Instance of SQL Server

Execute the below TSQL script for a renamed computer which hosts a named instance of SQL Server.

Use Master
GO

sp_dropserver <old_name\instancename>
GO
sp_addserver <new_name\instancename>, local
GO

Important Note: - Once you have executed the above TSQL script, go ahead and restart SQL Server Instance Service using SQL Server Configuration Manager.

Confirm Changes

To confirm the changes rerun the @@SERVERNAME built-in configuration function to return the name of local server that is running SQL Server.

SELECT @@SERVERNAME AS 'Server Name'
GO

For more information on how renaming a computer / server with a new name can affect Replication, Log Shipping, Data Mirroring refer the following MSDN Article.