When the Master Database is restored or moved to another server with a different a 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 in five tables in the MSDB database. The most recognizable table is: msdb.dbo.backupset. This may cause problems 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 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
--
ServerName_Problem
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
--
ServerName_Change
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
--
ServerName_Change_With_Named_Instance
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:
RestartSQLServer
After the SQL Server Instance has been restarted, verify using the same SQL queries listed above.
ServerName_Resolved