New answers tagged change-tracking
Are you looking to synchronize the data locally? Ideally, you would accomplish this through some form of replication from the source. If you have a local copy of all tables, you can create a script or stored procedure to check for records deleted, inserted, or updated by comparing to your copy of the data. This would have to be done on a table by table ...
You should separate the tests into tests that can execute inside a transaction and rollback and tests that require a full restore. Execute accordingly :) If you have any DDL in the test then don't bother with transactions, the only safe option is to restore. Most single threaded DML tests will work if wrapped in a transaction and rolled back, but not all. ...
The above given suggestion looks a good start-up into your mentioned scenario. However, I would like to refer you on this informative article that covers the auditing parts and helps to track all critical and granular changes on SQL server with real time alerts : http://sqlserverauditing.blogspot.in/2014/02/track-all-critical-and-granular-changes.html
Top 50 recent answers are included