This script is woefully out of date, but is still referenced by several websites. I recommend that you DO NOT USE IT.
I know that this script is ubiquitous across the ‘net, but whenever I google for it I come up with elaborate stored proc’s that are overkill for my needs – so here are the commands necessary to rename a SQL Server instance, for posterity:
— Get the current name of the SQL Server instance for later comparison.
— 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.
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.