This post is about how to rename a stand-alone SQL server instance. There could be some reasons to do that: You simply forgot to change the name of your Sysprep server or You want to change from the default instance name to another and etc.
The dynamic view sys.servers enables you to see the SQL server instances.
Before renaming a SQL Server instance you need to consider the following:
SQL failover cluster. You must evict the nodes and make the instances renaming. See this link for more info.
SQL server instance involved in replication is not allowed to be renamed.Mirroring. The mirroring between the instances must be first stopped in order to make the renaming successfully. Then you need to re-establish it.Keep in mind that renaming a sql server that uses SSRS, SSIS, SSAS may have availability issues. You need to check the configuration files for the services.
See some more information in this link as well.
The procedure is simple. You just need to run two stored procedures: sp_dropserver and sp_addserver.
The next code will make the name change
--Renaming a sql server
declare @old_server_name sysname;
declare @new_server_name sysname;
set @old_server_name = @@SERVERNAME;
set @new_server_name = CONVERT(sysname, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))+N'\'+CONVERT(sysname, SERVERPROPERTY('InstanceName'));
/* or if you want other name
set @new_server_name = CONVERT(sysname, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))+N'\'+'YourServerNameProposal';
*/
select * from sys.servers;
if @old_server_name != @new_server_name
begin
exec sp_dropserver @old_server_name;
exec sp_addserver @new_server_name, local;
raiserror('Restart the SQL Instance service for the change to take effect.', 10, 1) with nowait;
end
select * from sys.servers;
Note that you need to restart the instance after.
