Post reply

Identify Changed Records

  • May 29, 2019 at 3:54 pm

    #3648291

    I have a database that is refreshed from a backup nightly. I do not have access to the source database, nor the job that creates the backup.

    A few tables are large, ~40M rows. Rather than handle the entire table for ETL, I'd like to work with just the delta and new records. I have a couple questions:

    • If I enable change data capture on the database to which the backup is restored, and CDC is not enabled at the source, will this disable CDC on my database? ( my assumption is yes ).
    • Is there a way (other than snapshots) to cleanly and efficiently assess data changes between backups? Notes:

      • The modified dates in the database are not reliable
      • There is no audit table available
      • CDC is not enabled on the source data, and enabling it is not an option.

     

    Thanks

  • May 29, 2019 at 4:01 pm

    #3648300

    As you have no access to the source database and no way (in code) of identifying rows which have recently been added, modified or deleted, you are in a very difficult position.

    If there's no logic you can use to identify data changes in code, you will have to scan entire tables.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 29, 2019 at 4:24 pm

    #3648311

    Rough. I had a feeling that was the case.

     

    I may try and pivot to keeping the nightly ETL performance efficient rather than focus on parsing changes.

    Thanks

  • May 30, 2019 at 12:21 pm

    #3648539

    You could keep a second copy of the database around. That will give you a comparison point. You have to sacrifice storage, but disks are pretty cheap.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply