Take the 2-minute tour ×
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's 100% free, no registration required.

I need to know what has been changed on my database since last night. Is it possible to extract this data from the LDF file and to build a new Database that contains the delta?

For example, let say I have a table for users and now, a new user was added and one of the users update his home address. I need to be able to build a new database that users table will contain two records 1. The new user (and to add a new column to know if it’s new or update field) 2. The user that update his record (it will be nice to know which record has been update)?

BTW, I have to SQL servers that I can use (2008 and 2012)

Thanks In Advance

share|improve this question
    
Is this a one-time thing, or do you need to do this day-to-day? –  Jon Seigel Aug 7 '13 at 12:33
add comment

3 Answers 3

I know you asked about reading log files but I there are better strategies for dealing with slowly changing dimensions.

If you have the Enterprise Edition of SQL Server then you can use Change Data Capture. It needs to be set up on the database and tables beforehand but will then track inserts, updates and deletes.

You can use the lighter-weight Change Tracking.

Microsoft have published a comparison of Change Data Capture and Change Tracking.

You can use a rowversion column to implement high-watermark tracking. The nightly process records the current highest rowversion then queries the table for all rows with the rowversion greater than last night's highest value. This is for inserts and updates. If you wish to track deletes then you need to write delete triggers that change a "deleted" flag.

You can use triggers on the tables that copy the inserted, updated and deleted data into tracking tables. The nightly process reads then empties these tracking tables. As an aside, this is similar to how merge replication and updating-subscriber transactional replication work.

All of these methods require modification to the source database objects. I'm guessing from your question that this might not be a possibility. If that's the case, see VonPryz' answer.

share|improve this answer
add comment

If you are worried about unauthorized data modifications, consider implementing an auditing strategy.

As far as I know, there are no documented features that are of much help for post-change ad-hoc change detection if no auditing has been implemented.

One, tedious way would be to restore a previous version of the database to a different a name. Script both the old and current databases into text files. Pick the relative complement of the scripts and you will get the changes. If there has been multiple changes on same a row, only the final result is seen.

That being said, there are 3rd party tools available that can compare databases. There are tools that claim to be able to parse transaction logs too. ApexSql and Redgate seem to be popular picks. Whether any of these tools are any good, I cannot say.

In addition, there is an undocumented fn_dblog function, which could be used to read transaction logs.

share|improve this answer
    
Unfortunately this is not the case. I need to retrieve this data for BI solutions so I have to compare the changes made in the past 24 hours. –  LiranCo Aug 7 '13 at 12:23
    
I would definitely go for the 3rd party tools rather than reinventing the wheel. –  Aaron Bertrand Aug 7 '13 at 12:32
    
(Though RedGate's log reader tool only supports SQL Server 2000 IIRC.) –  Aaron Bertrand Aug 7 '13 at 12:33
add comment

ApexSQL Log can provide the list of transactions made in a specified time period. It has a time range filter where you can specify the last 24 hours, or any other period you want to read. Just make sure you provide enough transaction log backups to cover the specified time range.

ApexSQL Log can also create a redo script for all these transactions, so you can execute it against the new database where delta data will be stored.

What's equally important, if you want this to be a reoccurring job, you can schedule it using ApexSQL Log CLI and SQL Server Jobs.

Here's an example: Automating daily transaction log reading

Disclaimer: I work for ApexSQL as a Support engineer

share|improve this answer
add comment

Your Answer

 
discard

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.