SQL server clone and rename fiasco

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
       GO
       sp_addserver DEMOSERVER:
       GO
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?
wfssupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vadim RappCommented:
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.
0
Scott PletcherSenior DBACommented:
@@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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wfssupportAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.