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