SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Change the server name of the SQL Server Instance

By PremKumar Raju,

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. 

Total article views: 312 | Views in the last 30 days: 8
 
Related Articles
FORUM

changing the hostname

changing the hostname

FORUM

@@servername and serverproperty('servername') gives different values

select serverproperty('servername') and select @@servername shows different names

FORUM

Change @@servername in SQL 2005

@@servername does not reflect server properties name

FORUM

SQLCLUSTER @@servername = NULL

SQLCLUSTER @@servername = NULL

FORUM

How to track from which hostname the Store Procedure was changed

How to track from which hostname the Store Procedure was changed

Tags
 
Contribute