Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

migrated from May 8 '13 at 1:02

This question came from our site for professional and enthusiast programmers.

Are you looking for the data CDC had captured or the settings (which tables, etc. you had CDC configured for)? – Shawn Melton May 8 '13 at 14:13
@ShawnMelton I'm looking for the data the CDC had captured. – Ponml May 8 '13 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. – Shawn Melton May 9 '13 at 11:30
up vote 0 down vote accepted

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.

share|improve this answer

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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