Script to change the SERVERNAME property in SQL Server

When the Master Database is restored or moved to another server with with a different hostname, the master database is unable to detect that it is on a new server.  The SERVERNAME property will need to be updated with the new server’s hostname.

This SERVERNAME property is written to five tables in the MSDB database.  Probably the most recognizable table is: msdb.dbo.backupset.  This may cause problem if you are using a 3rd party monitoring or backup software.

To check that the SQL Server’s SERVERNAME matches the operating system’s HOSTNAME.  The queries below (nice for cut & paste) will reveal if there is a difference.  Like in the picture below,  SQL Server has the hostname (or SERVERNAME) as “SQL2014”, and the actual operating system hostname is “PRODUCTION”.

Please Note:  the web browser may change some of the characters below,  please check to match the script to the picture below

 --
 --  The Hostname SQL Server recorded
 --
 select @@SERVERNAME as "SQL SERVER HOSTNAME"
 go
 --
 --  The Hostname pulled from the operating system
 --
 select SERVERPROPERTY('MachineName') as HOSTNAME
 go
 --

The scripts provided grab the hostname from the operating system, and loads it into a variable.  Then it drop the old SERVICENAME, and then it adds back the SERVERNAME as the operating system’s hostname.  Nice cut & paste, because you do not have input anything.

Script shown below can be used to change the SERVERNAME for a default instance:

 --
 -- Declare Variable
 --
 DECLARE @new_name nvarchar(50)
 --
 -- Push OS Hostame into the variable
 --
 set @new_name = convert(nvarchar(50), SERVERPROPERTY('MachineName'))
 --
 -- Drop the hostname in SQL Server
 --
 exec sp_dropserver @@servername
 --
 -- Add the new hostname in SQL Server from variable
 --
 exec sp_addserver @new_name,'local';
 go
 --

Script shown below can be used to change the SERVERNAME for a named instance:

 --
 -- Declare Variable
 --
 DECLARE @new_name nvarchar(50)
 --
 -- Push OS Hostame into the variable
 --
 set @new_name = convert(nvarchar(50), SERVERPROPERTY('MachineName'))
 --
 -- Add Named Instance to the string
 --
 set @new_name = @new_name + '\' + convert(nvarchar(50), SERVERPROPERTY('InstanceName'))
 --
 --
 -- Drop the hostname in SQL Server
 --
 exec sp_dropserver @@servername
 --
 -- Add the new hostname in SQL Server from variable
 --
 exec sp_addserver @new_name,'local';
 go
 --

After the SERVERNAME property has been updated, the SQL Server Instance will need to be restarted for the SERVERNAME property to take effect.  The configuration manager is one of many way to restart the the SQL Server Instance.

Highlight the Instance in the SQL Server Configuration Manager, then click on the restart button, like pictured below:

After the SQL Server Instance has been restarted, verify using the same SQL queries listed above.