SQL server clone and rename fiasco

Posted on 2014-07-14
Last Modified: 2014-09-25
We wanted to take a Windows 2008 R2 server named DEMOSERVER with a SQL server name of DEMOSERVER and clone it to see if we could allow multiple users to log onto the same server (terminal services) instead of having to build out a series of fresh clients each time the software on the server needed to be updated and demonstrated again.  We took DEMOSERVER and made a clone of it called DEMOSERVER1.  I went through the registry locations and config files for the custom installed software and changed all that to point to its own DEMOSERVER1 name.  I then figured I should change SQL's identity to match the DEMOSERVER1 name.  I made 2 mistakes in this process:
1)  I accidentally left the original DEMOSERVER name when logging in to SSMS.
2) I goofed the command to rename the server:
   A) sp_dropserver DEMOSERVER
       sp_addserver DEMOSERVER:
I ran it a couple times before I realized I had some things not quite right.  I was getting messages on DEMOSERVER1 that the SQL name DEMOSERVER no longer existed, but when I ran select @@servername as 'Server Name'; it kept showing the old server name.  I became concerned that the old SQL server name was goofed up, but when I run select @@servername as 'Server Name'; AND sp_helpserver AND select * from sys.servers; commands, they all make it appear normal, but I am not 100% sure.  DEMOSERVER1 on the other hand, I know is messed up.  I tried adding DEMOSERVER1 while forgetting the local switch, and eventually got the DEMOSERVER name dropped.  However, DEMOSERVER1 does not show as the local instance (NULL) but I could not get it to drop DEMOSERVER1 either.  However, after more fiddling around and experimentation, I now get the following results:
1)  select @@servername as 'Server Name' --returns: Server Name NULL
2) select * from sys.servers --returns: server_id 0 = DEMOSERVER2
                                                               server_id 1 = DEMOSERVER1
3) sp_helpserver --returns Name                       Network_Name       ID
                                            DEMOSERVER1         DEMOSERVER1       1
                                            DEMOSERVER2         DEMOSERVER1       0

Can anyone give me advice on how to doublecheck my original DEMOSERVER to make sure I didn't hork that up and also how to fix the disaster I made on DEMOSERVER1?
Question by:wfssupport
    LVL 40

    Expert Comment

    by:Vadim Rapp
    Nothing of this is required at all. add/dropserver (which in fact are obsolete) applies to the linked servers. But if you take database file from server1 and attach it to server2, no changes are required.

    Сlient applications can be easily and transparently switched from one server to another using sql client utility.

    If you indeed have linked servers, probably the most straightforward way is to delete all linked servers pointing to this disaster duo, and re-add them the way you want.
    LVL 68

    Accepted Solution

    @@SERVERNAME requires a restart before it reflects a new name.  Don't trust it.

    Instead, use:
    SERVERPROPERTY ( 'InstanceName' )
    which should reflect changes automatically.  Note that this function returns NULL for the default SQL instance.

    On DEMOSERVER, do the SELECT * FROM sys.servers, as you're doing, but also use SERVERPROPERTY() with different propertynames to check it further.  Finally, verify in msdb.dbo.sysjobs that the originating_server_id is 0.

    On DEMOSERVER1, you might need to stop and start SQL.  Then drop all the server names, add the local name, then add any linked server(s) if you want to.  Use the same verifications as above to check it.

    Author Closing Comment

    Unfortunately, by the time I was able to take another look at the issue and attempt the suggested solution, the trial licensing on the server had expired.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now