Replication Failure - Recovering Data
-
Situation:
SQL Data replication did not exist and a complete DB backup was performed. Windows 2000 Server systmes with Windows 2000 SQL Server were re-built on 2 separate servers that exist in different cities - identical Hardware, SP's, etc.
At the time of the rebuild, the following replication was implemented - 1 Publisher and 2 Subscribers with the Distribution & Publication databases on the same box.
However, at the time of creating the Replication topology, each table was defined as having a unique but the Range Identity configuration was not implemented.
As a result, when the first subscription site merged with the Publication database, all was fine. But when the second site, scheduled later than the first, began it's merge update function and with each of the databases having a unique identifier when the records were created, the second site's information was logged as a conflict and all procedure according the "first come - first serve" resolution put into place.
-
Doesnt sound like fun. No easy way to unravel that. Got a plan in mind yet?
Andy
-
Hi Andy,
No easy way - ok, what's the hard way?
Currently, each of the subscriber sites are inputting data into their respective databases and I was hoping that it might be possible to run a differential script (which I have) between the Publisher DB and each of the subscriber DB tables. The publisher DB has remained static as no one accesses this DB directly - I'm hoping this is a good thing.
With the specific records extracted from a subscriber, is it possible to script INSERTS to update the Publisher DB? If this is possible, what issues do I need to be aware of and prepare for?
I have the diagram of all the tables and their relationships. I'm hoping that this would be helpful in writing the script. In looking at it, I notice that there is what appears to be a "chain" at one end of a line and a "key" at the other. What is the meaning of these symbols? A description doesn't seem to be easily found in BOL.
Any help in this is greatly appreciated. This just happened on Monday this week so there is only 3 full days of entries at each subscriber site. I suspect the sooner I get on this, the less likely it is for problems.
Chris.
-
How could the publisher db be static if you're using merge replication?? If somehow it is you have a chance.
The symbols indicate a foreign key relationship.
Depending on your answer to my first question you may be in deeper than you think. If you've got three servers that potentially each have a different record with pkey=1 and lots of related records based on that key, then you merge, changes from servera/rec1 will get updated into serverb/rec1. At that point your information is corrupt.
Andy
-
The merge publication was removed as soon as the problem was detected on Tuesday morning. Only one of the sites successfully completed a merge. When it was discovered that there was a problem, the second site's data was restored from the transaction log. Since then, each site has been accessing their subscription DB. There are only 2 servers involved.
When replication was setup, a server at one site was setup as a Publisher/Distributor. Then both sites were setup with subscription DBs. So, the Publisher site contains the Publisher DB in addition to a suscription DB and the second site simply has a subscription DB.
-
Gotcha. Ok, so what you need is to get the servers using a new id range. If you've got a lot of foreign keys that are actually documented, probably the easiest way is to enable cascading updates. Then update the pkey of the main record and let it happen from there. If not you'll have to build a table that maps origid to newid, then work out a series of scripts that will change the keys without breaking everything. May be easier to remove the fkeys if you go that route.
I'd recommend copying the structure of all the tables to a new db, putting just a couple rows in, work through your scripts there. Lot faster to find your mistakes that way than when processing thousands of rows.
Andy
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply