0

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?.

jarlh
  • 42,561
  • 8
  • 45
  • 63
ManiMania
  • 1
  • 1
  • 2
    Is there a reason you're rebuilding transactional replication rather than using the built-in version? – Damien_The_Unbeliever Feb 09 '22 at 13:42
  • Our databases size is huge and we want to apply the transactional data to the downstream process which would take less time. – ManiMania Feb 09 '22 at 13:58
  • All possible with Transactional Replication. See eg [Init from backup](https://learn.microsoft.com/en-us/sql/relational-databases/replication/enable-initialization-with-backup-for-transactional-publications?view=sql-server-ver15#:~:text=To%20initialize%20a%20subscription%20to%20a%20transactional%20publication,of%20the%20Publication%20Properties%20-%20%3CPublication%3E%20dialog%20box) and [Distribution Retention](https://learn.microsoft.com/en-us/sql/relational-databases/replication/set-distribution-retention-period-for-transactional-publications?view=sql-server-ver15) . – David Browne - Microsoft Feb 09 '22 at 14:24

1 Answers1

0

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.

Hein
  • 1,453
  • 8
  • 8