SQL server clone and rename fiasco
Posted on 2014-07-14
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
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?