Post reply

@@SERVERNAME Returning Old Name After Restore

  • February 22, 2018 at 11:27 am

    #344164

    I'm testing a couple things in our UAT environment and one of the things I did was backup the system databases on the existing default instance, remove that instance, install a new named instance, and restore the system databases to that named instance.  The instance is installed on an active-passive cluster.  I noticed that when I ran select @@servername it returned the old server name which happens to be the virtual node name (NODENAME) since it was a default instance.  However, when I ran select SERVERPROPERTY('servername') I noticed that it returned the the full named instance name (NODENAME\INSTANCE).  I also noticed that when I look at the securables for a login it will correctly list the name (NODENAME\INSTANCE) in red circle 1 whereas it will use the old name (NODENAME) in red circle 2. 

    Is this anything to worry about?  If so, how can I change it so that everything displays NODENAME\INSTANCE?

  • February 22, 2018 at 7:06 pm

    #1980882

    hello RonMexico -- I think this has happened to me before. I think the name is retained for obvious reasons because of the master DB restore. I normally keep the instance name the same and most things too if I were to restore the master DB just to be on the safe side.

    Given your question it is possible to change the name using sp_dropserver and sp_addserver (scary names I know!) https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server

    I would not exactly know the complete impact of doing so. i would suggest another backup of master so you can restore and rollback again if anything odd happens.

    hope this helps
    ivan

  • February 23, 2018 at 7:39 am

    #1980927

    Thanks, Ivan! That did the trick.  I stumbled across that prior to posting but I was reluctant to try because 1.) the name is quite scary and 2.) the article talked about changing it for a stand-alone instance and I'm dealing with a cluster.  So far so good though in my brief testing.

  • February 23, 2018 at 8:27 am

    #1980943

    Should be the same procedure for the cluster. I avoid restoring master unless I really need it, preferring to move over the logins/linkedservers/etc if necessary.

    Let us know if anything seems to be off. This is certainly something many people would be wary of doing.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply