'Sql Server CDC (Change Data Capture) Apply the data changes to the backedup database

We had a requirement to enable all tables CDC and migrate the updates to the database backup which were taken prior to enabling CDC.

  1. Step 1: Take the database backup at x point time.
  2. Step 2: Enable CDC on the database and Enable CDC on all the tables.
  3. Step 3: After the Y point of time we would need to merge data changes to the database backup we took at step 1. i.e (X to Y point of time changes needs to be merged to the backed-up database.)

Could you please help me out on this?.

sql-serverssmschange-data-capture


Solution 1:[1]

Well, for starters you have step 1 and 2 in the wrong order. You should start CDC before making the reference backup because the other way around there is a gap of missing changes. It is better to have duplicate changes than missing change would you not agree? (dup insert gives dup error, dup update just re-applies, dup delete just finds nothing to delete which is fine).

There are several (for fee) 3rd party solutions to handle this solution, and possibly some shareware. Please study your options before reinventing the wheel so to speak with all the possible errors that go with that. One such solution is Qlik Replicate which they picked up from Attunity (where I used to work). Good luck. Hein.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Hein

Related Questions