Renaming SQL server instance

– Get the current name of the SQL Server instance for later comparison.
SELECT @@servername

– Remove server from the list of known remote and linked servers on the local instance of SQL Server.
EXEC master.dbo.sp_dropserver ‘[SERVER NAME]‘

– Define the name of the local instance of SQL Server.
EXEC master.dbo.sp_addserver ‘[NEW SERVER NAME]‘, ‘local’

– Get the new name of the SQL Server instance for comparison.
SELECT @@servername

And a few notes relating to their usage:

  • sp_dropserver: 1. this stored procedure can remove both remote and linked servers; 2. using the droplogins parameter indicates that related remote and linked server logins for [SERVER NAME] must also be removed. More info.
  • sp_addserver: 1. to define a linked server, use sp_addlinkedserver (as this functionality will be depreciated in version above SQL Server 2005); 2. sp_addserver cannot be used inside a user-defined transaction. More info.

I would also recommend the following when renaming a SQL Server:

  • If you are performing this task as part of a machine rename, rename the machine first then rename the SQL Server.
  • Once you have renamed the SQL Server, do a full restart of SQLServer, including any dependant services.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s