locked
How to recover CDC data from Trans Log backups RRS feed

  • Question

  • We have a table with CDC enabled and capturing data. We perform a Full backup every 24hrs and trans log backup every 15 min. We had to restore from a backup and all its trans logs to a point and everything was restored correctly but the KEEP_CDC was not used and the Capture/Cleanup jobs weren't enabled. DB went live and 20 hours later, we realized that CDC wasn't captured.

    I'm trying to now restore the DB from the Full backup once the system went live and all the trans logs to the point when CDC jobs got enabled. I used the NORECOVERY on the DB restore and on ALL the trans Log restore EXCEPT the last Trans log where I used KEEP_CDC.

    When I looked inside the CDC table, it appears that it only captured the data from the last transaction log which was about 34 records instead of thousands.

    What is the correct step by step process to recover the transaction log data into the CDC table during a RESTORE from MULTIPLE trans log backups?

    Note that the CDC table was NOT being captured/populated during these 20 hours but I have the trans logs for that. If I had the option to RESTORE the DB, turn ON the CDC jobs and then RESTORE each trans log one at a time, that would have been perfect but I know this is not possible since the NORECOVERY puts the DB in nonoperational mode and KEEP_CDC after a DB Restore enables the jobs but won't allow me to continue with the Transaction Log restoration.

    Thanks

    Anatole

    Friday, March 18, 2011 7:02 AM

Answers

  • If CDC was enabled and the jobs were not running, the commands should be written to the log and marked to be read, but where never read. Restoring back before the point you disabled CDC on that table might allow the jobs to pick them up.

    It looks like when you try to re-enable CDC on this table it does detect that it is already enabled.

    I would guess that cdc is in an inconsistent state and its hard to get in functionining again.

    If the data is critical to you, you might want to open a support incident with Microsoft to have them help you to retrieve it.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Sunday, March 20, 2011 5:35 PM

All replies

  • When a table is enabled for CDC, the DML from that table is written to the log with special markers telling the CDC process to pick them up and write them to the CDC tables. Once they are read a marker is put in the top of the log saying that this command/transaction/DML has been picked up.

    If you did not have CDC enabled, these transactions are written to the log WITHOUT being marked  to be picked up by the CDC process and hence no amount of restoring logs will enable them to be picked up.

    If you really need these commands to be picked up now by the CDC process you are best to delete the rows and then reinsert them. Updating them will merely have them picked up as updates which may or may not work for you.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Friday, March 18, 2011 10:56 AM
  • To read more about CDC internals check out this link: http://msdn.microsoft.com/en-us/library/cc645937.aspx

    Read the section entitled Relationship Between the Capture Job and the Transactional Replication Logreader


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Friday, March 18, 2011 11:00 AM
  • Thanks for the replies, Hilary.

    The table had CDC enabled but it was in a strange state.

    The jobs weren't there/enabled (I'm trying to remember which one was the case). But I clearly recall checking the DB and table. The DB is_cdc_enable was returning a 1

    and when I tried to sp_cdc_enable_table with the same exact parms as when I created/enabled the CDC on my table, it would give me an error stating that this particular source_schema/name is already in use and I need to use another one. Therefore not creating the capture/cleanup jobs again.

    My only option was to make a backup of the cdc table (I do that whenever I need to update/delete data) and run sp_cdc_disable_table and then again the sp_cdc_enable_table. Of course this created a new/empty CDC table and the two new jobs.

     

    So the CDC was enabled on the table but the jobs weren't running. The I restored the DB, on my third attempt, I made sure the jobs were running/working properly. For the restore, the jobs needed to be disabled since the jobs had a lock on the DB and RESTORE was complaining. So I did that and restored the DB. If I recall, I then enabled the jobs and started to restore the transaction logs.

    It's been a long journey and multiple attemps and I need to go back and confirm that my transaction logs did not include the steps I took to reset the CDC which may explain that the DB was reset to empty and only few entries are in the table. I need to retry the RESTORE DB and this time only restore handfull of logs and then stop and check the content. If the CDC table is being populated, then I'm good and just need to get restore the DB to the correct transaction log.

    I'll try to do that but hopefully the above give you some other hint as what was the state of the table and if the restore should have still picked up the entries even though the jobs didn't exists during the transaction log backups but were there during the RESTORE since I created them during my DB restore operation.

     

    Thanks

    Anatoile

    Sunday, March 20, 2011 4:16 PM
  • If CDC was enabled and the jobs were not running, the commands should be written to the log and marked to be read, but where never read. Restoring back before the point you disabled CDC on that table might allow the jobs to pick them up.

    It looks like when you try to re-enable CDC on this table it does detect that it is already enabled.

    I would guess that cdc is in an inconsistent state and its hard to get in functionining again.

    If the data is critical to you, you might want to open a support incident with Microsoft to have them help you to retrieve it.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Sunday, March 20, 2011 5:35 PM