Technical Article

Change the server name of the SQL Server Instance

,

When a VM is provisioned from the VM template, the server name and host name doesn’t match with each other. In such scenario you may need to change the server name of the database instance.

Below script will be handy when then change is performed. This script will change the server name of the SQL server Instance, if the host name doesn't match the server name. 

DECLARE @HostName NVARCHAR(30),

        @ServerName NVARCHAR(30)

SELECT @HostName=HOST_NAME()

SELECT @ServerName=@@SERVERNAME

SELECT @HostName AS 'HostName',@ServerName AS 'ServerName'

IF @HostName <> @ServerName

BEGIN

USE [master]

EXEC sp_dropserver @server=@ServerName

EXEC sp_addserver @server=@HostName,@local='local'

SELECT @HostName=HOST_NAME()

SELECT @ServerName=@@SERVERNAME

SELECT @HostName AS 'HostName',@ServerName AS 'ServerName'

END

ELSE

SELECT @HostName AS 'HostName',@ServerName AS 'ServerName'

GO

Restart the SQL Server Instance once the above script is executed. 

DECLARE @HostName NVARCHAR(30),
        @ServerName NVARCHAR(30)
SELECT @HostName=HOST_NAME() 
SELECT @ServerName=@@SERVERNAME 
SELECT @HostName AS 'HostName',@ServerName AS 'ServerName'

IF @HostName <> @ServerName
BEGIN
USE [master]
EXEC sp_dropserver @server=@ServerName
EXEC sp_addserver @server=@HostName,@local='local'
SELECT @HostName=HOST_NAME()
SELECT @ServerName=@@SERVERNAME
SELECT @HostName AS 'HostName',@ServerName AS 'ServerName'
END
ELSE
SELECT @HostName AS 'HostName',@ServerName AS 'ServerName'
GO

Rate

3.67 (3)

Share

Share

Rate

3.67 (3)