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


Sept 16, 2012

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.

Reference: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

Conclusion

In this article you have seen how to rename a host computer when SQL Server in already installed.



Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."