[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL server clone and rename fiasco

Posted on 2014-07-14
Medium Priority
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
ID: 40197753
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 70

Accepted Solution

Scott Pletcher earned 1500 total points
ID: 40197851
@@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

ID: 40345042
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

873 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