{"id":652,"date":"2014-06-26T01:43:23","date_gmt":"2014-06-26T01:43:23","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=652"},"modified":"2014-11-19T11:56:50","modified_gmt":"2014-11-19T11:56:50","slug":"script-to-change-the-servername-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.theSQLReport.com\/?p=652","title":{"rendered":"Script to change the SERVERNAME property in SQL Server"},"content":{"rendered":"

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.\u00a0 The SERVERNAME property will need to be updated with the new server’s hostname.<\/p>\n

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

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

Please Note: \u00a0the\u00a0web browser may change some of the characters below, \u00a0please check to match the script to the picture below<\/strong><\/em><\/p>\n

 --\r\n --\u00a0 The Hostname SQL Server recorded\r\n --\r\n select @@SERVERNAME as \"SQL SERVER HOSTNAME\"\r\n go\r\n --\r\n --\u00a0 The Hostname pulled from the operating system\r\n --\r\n select SERVERPROPERTY('MachineName') as HOSTNAME\r\n go\r\n --<\/pre>\n
\"ServerName_Problem\"<\/a><\/p>\n
The scripts provided grab the hostname from the operating system, and loads it into a variable.\u00a0 Then it drop the old SERVICENAME, and then it adds back the SERVERNAME as the operating system’s hostname.\u00a0 Nice cut & paste, because you do not have input anything.<\/p>\n
Script shown below can be used to change the SERVERNAME for a default instance:<\/strong><\/p>\n
 --\r\n -- Declare Variable\r\n --\r\n DECLARE @new_name nvarchar(50)\r\n --\r\n -- Push OS Hostame into the variable\r\n --\r\n set @new_name = convert(nvarchar(50), SERVERPROPERTY('MachineName'))\r\n --\r\n -- Drop the hostname in SQL Server\r\n --\r\n exec sp_dropserver @@servername\r\n --\r\n -- Add the new hostname in SQL Server from variable\r\n --\r\n exec sp_addserver @new_name,'local';\r\n go\r\n --<\/pre>\n
\"ServerName_Change\"<\/a><\/h5>\n
Script shown below can be used to change the SERVERNAME for a\u00a0named instance:<\/strong><\/p>\n
 --\r\n -- Declare Variable\r\n --\r\n DECLARE @new_name nvarchar(50)\r\n --\r\n -- Push OS Hostame into the variable\r\n --\r\n set @new_name = convert(nvarchar(50), SERVERPROPERTY('MachineName'))\r\n --\r\n -- Add Named Instance to the string\r\n --\r\n set @new_name = @new_name + '\\' + convert(nvarchar(50), SERVERPROPERTY('InstanceName'))\r\n --\r\n --\r\n -- Drop the hostname in SQL Server\r\n --\r\n exec sp_dropserver @@servername\r\n --\r\n -- Add the new hostname in SQL Server from variable\r\n --\r\n exec sp_addserver @new_name,'local';\r\n go\r\n --<\/pre>\n
\"ServerName_Change_With_Named_Instance\"<\/a><\/p>\n
After the SERVERNAME property has been updated, the SQL Server Instance will need to be restarted for the SERVERNAME property to take effect.\u00a0 The configuration manager is one of many way to restart the\u00a0the SQL Server Instance.<\/p>\n
Highlight the Instance in the SQL Server Configuration Manager, then click on the restart button, like pictured below:<\/p>\n
\"RestartSQLServer\"<\/a><\/p>\n
After the SQL Server Instance has been restarted, verify using the same SQL queries listed above.<\/p>\n
\"ServerName_Resolved\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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.\u00a0 … Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":657,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[35,34,64],"class_list":["post-652","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-hostname","tag-servername","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/652"}],"collection":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=652"}],"version-history":[{"count":17,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/652\/revisions"}],"predecessor-version":[{"id":661,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/652\/revisions\/661"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/657"}],"wp:attachment":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=652"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=652"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=652"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}