February 2, 2012 at 9:47 am
Hope everyone are well.
Maybe you can help me with my problem.
I have a Publisher database in Sql Server 2008 and I have 2 subscriptions for it. My problem is that one of the subscriptions server had to be replaced due to issues and now I have to restore the databases and in Publisher I can have lets say 100.000 rows in one table and in subscribers I can have one billion rows. I was thinking in backup the other subscriber and restore in this new one, my problem is that when I start the subscription, the snapshot overwrites entire database and instead of having the billion rows I just have 100.000!
What can I do to start the subscription without overwriting the data that is already in database? Is there any way? And of course, without any downtime... 🙁
Thanks in advance for all your help!
February 2, 2012 at 10:03 am
You can use sp_addsubscription with @sync_type = N'none' or @sync_type=N'replication support only'
none will do nothing and the subscriber side replication procedures will be untouched.
replication support only will regenerate the subscriber side replication procedures will be untouched.
Neither will generate a snapshot an overwrite subscriber data. You may get replication errors for you to correct for missing rows when they get updated or deleted. You will need to DTS these over to the subscriber.
February 3, 2012 at 5:21 am
Thank you MisteryJimbo for your quick answer! 🙂
I have done it successfully without any kind of issues, my concern now is that after talking with a colleague it seems that this type of procedure can give deadlocks after a while if we try to change something in an article. Is it true? If so, what can be the solution for it?
Thanks in advance for all your help!
February 3, 2012 at 6:57 am
Completely unfounded. Deadlocks would only occur on the subscriber if the data were being modified (unlikely). There's no more chance now than there was before
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply