September 27, 2014 at 8:27 pm
I needed to duplicate one of our Production servers in Staging, so I installed a new copy of SQL Server 2012 SP1 on a new Win 2012 box with a different server name and instance name, backed-up all databases on Prod, then restored Master, MSDB, then all user databases. At first glance everything came up fine, but running @@ServerVersion still returns the old server name which is throwing off some third party services (CRM namely).
So when I ran Sp_dropserver to drop the old name I get this error:
Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
There are still remote logins or linked logins for the server '<old server name>\<old instance name>'.
And I found the Remote Login it's referring to is Replication because Replication was running on the Prod server. So I tried to remove replication, and it errored out due to this same issue.
So my first attempt at a work around was to add an alias to the old server in SQL Server Configuration Manager and connect to the 'old' server name which actually connected to the new server. I also updated the Hosts file on the new server so the old server name was aliased to the new server IP just in-case.
This worked, I connected, but when I run this from the Distributor database:
exec sp_dropdistributor @no_checks = 1
I get this:
Msg 21482, Level 16, State 1, Procedure sp_MSdrop_subscriber_info, Line 28
sp_MSdrop_subscriber_info can only be executed in the "distribution" database.
Msg 14042, Level 16, State 1, Procedure sp_MSrepl_dropsubscriber, Line 311
Could not create Subscriber.
Something to note, when I restored the Distributor database I had REPLACE selected so it removed this database from the System Database folder. I don't know if this matters.
So any suggestions on how to either remove this Remote Login or force Replication to be disabled which in turn will hopefully remove the remote login?
Thanks - Sam
September 28, 2014 at 4:33 am
To be honest, I'd recommend you rebuild the system databases and then re-apply logins, linked servers, jobs, etc. There's a reason why it is strongly not recommended to restore the system databases from one server to another.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2014 at 5:04 am
samalex (9/27/2014)
I needed to duplicate one of our Production servers in Staging, so I installed a new copy of SQL Server 2012 SP1 on a new Win 2012 box with a different server name and instance name, backed-up all databases on Prod, then restored Master, MSDB, then all user databases. At first glance everything came up fine, but running @@ServerVersion still returns the old server name which is throwing off some third party services (CRM namely).So when I ran Sp_dropserver to drop the old name I get this error:
Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
There are still remote logins or linked logins for the server '<old server name>\<old instance name>'.
And I found the Remote Login it's referring to is Replication because Replication was running on the Prod server. So I tried to remove replication, and it errored out due to this same issue.
So my first attempt at a work around was to add an alias to the old server in SQL Server Configuration Manager and connect to the 'old' server name which actually connected to the new server. I also updated the Hosts file on the new server so the old server name was aliased to the new server IP just in-case.
This worked, I connected, but when I run this from the Distributor database:
exec sp_dropdistributor @no_checks = 1
I get this:
Msg 21482, Level 16, State 1, Procedure sp_MSdrop_subscriber_info, Line 28
sp_MSdrop_subscriber_info can only be executed in the "distribution" database.
Msg 14042, Level 16, State 1, Procedure sp_MSrepl_dropsubscriber, Line 311
Could not create Subscriber.
Something to note, when I restored the Distributor database I had REPLACE selected so it removed this database from the System Database folder. I don't know if this matters.
So any suggestions on how to either remove this Remote Login or force Replication to be disabled which in turn will hopefully remove the remote login?
Thanks - Sam
Don't restore system databases across instances especially where you've changed the server and instance name and where replication is concerned. Also note that MSDB holds server name information so any scheduled agent jobs would likely fail. I'm guessing that you are running the sql server under a new account too. This means the SMK will also be unable to decrypt the stored login information for the replication publications\subscriptions.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 28, 2014 at 9:36 am
Hi everyone,
Thanks for the advice. I've made similar restores without issue with just running sp_dropserver and sp_addserver to fix the server name, but this was my first time to do one where Replication was involved. The biggest reason I hoped to restore the entire system was because this database runs CRM and there are some customizations that get installed into SQL when CRM is installed that I hoped to carry over to the new server.
At this point I agree rebuilding the server from scratch and just restoring the User databases is probably the only option. I've found some documentation on how to recreate the CRM objects, I just hoped to have our Staging and Prod environments as identical as possible.
Thanks again...
Sam
September 28, 2014 at 10:18 am
samalex (9/28/2014)
I've made similar restores without issue with just running sp_dropserver and sp_addserver to fix the server name
I would say you've been lucky up til now
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 29, 2014 at 2:16 pm
Interesting... I guess I never thought about the implications of replication when thinking about disaster recovery.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply