sqlserver修改主机名

一、修改操作系统主机名

我的电脑—属性—更改设置

二、登录SQLServer查看服务器名称

https://learn.microsoft.com/zh-cn/previous-versions/sql/sql-server-2005/ms174396(v=sql.90)
https://learn.microsoft.com/zh-cn/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-ver17&redirectedfrom=MSDN

1.检查服务器名称

检查SQL Server中的“服务器名[/命名实例名]”, 和当前真实的“计算机名[/命名实例名]”。如果修改了计算机名，则这两者即会不一致

1
2
3

select @@serverName as '旧服务器名称',  serverproperty('serverName') as '新服务器名称';
select * from sys.servers;
exec sp_helpserver;

2.更新服务器名称

脚本:
IF SERVERPROPERTY('servername') <> '新服务器名称替换' 
BEGIN
    DECLARE @server_name NVARCHAR(128)
    SET @server_name = '新服务器名称替换'
 
    EXEC sp_dropserver @server = '旧服务器名称替换'
    EXEC sp_addserver @server = @server_name, @local = 'LOCAL'
END



IF SERVERPROPERTY('servername') <> 'WIN-DFPAC123' 
BEGIN
    DECLARE @server_name NVARCHAR(128)
    SET @server_name = 'WIN-DFPAC123'
 
    EXEC sp_dropserver @server = 'WIN-JAHMPDMRON8'
    EXEC sp_addserver @server = @server_name, @local = 'LOCAL'
END


全自动脚本:
IF @@SERVERNAME <> SERVERPROPERTY('SERVERNAME')
BEGIN    
  EXEC sp_dropserver @server = @@SERVERNAME
  DECLARE @new_server_name SYSNAME

  SELECT @new_server_name = CAST(serverproperty('servername') as SYSNAME)
  EXEC sp_addserver @server = @new_server_name , @local = 'local'
END