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. It only takes a minute to sign up.
Sign up to join this community
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
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
