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
 --
 
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

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-1) was last changed on 24-May-2017 15:30 by Hyve Support
G’day (anonymous guest) My Prefs
  • View Page Source
  • This clear IPSec security association,
    clear ipsec sa peer X.X.X.X
    

All Pages

Page views: 1561

Private Tomcat

Linux

MySQL

Email

SQL Server

ASP

JSP

C#

Web Mail

Windows Plesk

Linux Plesk

PHP

Gaming

ASP.NET

Persits ASPUpload

Wiki Help

Referring Pages:
...nobody

JSPWiki v2.8.1