Is there anyway to restore CDC tables from transaction logs? My basic understanding is that CDC reads these logs, and entries that are specially marked, are grabbed by CDC. Once this has happened though, this data is no longer flagged for harvesting by CDC.

I need this to happen because a DB was restored without any KEEP_CDC options.

MS SMS, 2008 R2, sql server 10.5.16

  • Are you looking for the data CDC had captured or the settings (which tables, etc. you had CDC configured for)?
    – user507
    May 8, 2013 at 14:13
  • @ShawnMelton I'm looking for the data the CDC had captured.
    – Ponml
    May 8, 2013 at 15:19
  • The only way to get the data is going to be restoring the database, maybe to a second server, with the correct options so you can pull the data out. That way you at least have it for historical purposes.
    – user507
    May 9, 2013 at 11:30

2 Answers 2


Transaction logs are used as a source to get the data CDC uses but it would be very difficult to extract data yourself and insert it into CDC tables.

Problem is that transaction log is not well documented and there are basically two functions you can try DBCC LOG and fn_dblog. Another option is to use a third party log reader such as ApexSQL Log or Quest Toad but even with those tools you’ll be only able to read what’s written in transaction log. It would be up to you to take this data and somehow add it to CDC tables.


Well indeed, if the log is removed - blown away by the restore, and the changes were not yet applied then those changes are permanently lost. The core data is still out there though right? So you could consider a full load for suspect tables.

What is known about there changes suspect to be missing? Can they be identified through some flag column or date indicator perhaps? Perhaps you can come up with a query for a benign 'tickle' of candidate rows? UPDATE suspect_table SET some_column = some_column WHERE modified_date_column > 'some date';

As long as executed in defensive mode this will catch INSERTS ane UPDATES. Is would not see DELETES.

hth, Hein

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.