CODEWITHSUNDEEP
sql-serversql-server-2008-r2
chhenning
chhenning

Reputation: 2077

How to record all updates and insertions for a database for a specific time period

I have a remote db for which I like to record all changes ( updates and inserts ) for the next couple of hours. If necessary I like to revert all changes as well. Note, that all of the changes might be coming in from several users/applications.

The db is fairly small ( couple of GBs ) and the number of updates should be small too ( < 1000 ).

How would I do that with SQL Server 2008 R2?

Let me know if you need more information.

Thanks, Christian

Upvotes: 1

Views: 98

Answers (1)

Jon Seigel
Jon Seigel

Reputation: 12401

So many options, depending on your exact needs and what you have available:

  • Take a full backup when the database is in the "before" state. Use a data comparison tool to determine what changed.

  • Create a database snapshot (Enterprise Edition engine only). This will let you see the before state at all times, and you can manually consolidate/rollback/whatever the changes that get made, or revert to the snapshot which will essentially do the same thing as a restore. Use a data comparison tool to determine what changed.

  • Use Change Data Capture (CDC) (Enterprise Edition engine only). This will capture all the changes made to the tables on which it's enabled. This is a really granular approach. Combine with either a backup or snapshot to roll back.

  • Set up triggers on all the tables that can be affected, and direct the data to change tracking tables you create yourself. Combine with either a backup or snapshot to roll back.

Upvotes: 1

Related Questions