Is there an easy way to track all changes in a SQL 2005 Database

Question

Hey,

I've been tasked with hooking in our product with another third party product. One of the things I need to do is mimic some of the third-party's product functionality when adding new "projects" - which can touch several database tables. Is there any way to add some kind of global hook to a database that would record all changes made to data?

I'd like to add the hook, create a project using the third-party application, then check out what all tables were affected.

I know it's more than just new rows as well, I've come across a number of count fields that look to be incremented for new projects and I worry that there might be other records that are modified on a new project insert, and not just new rows being added.

Thanks for any help ~Prescott

Answer 1

Make sure the log will not be reused:

• the database is in full recovery mode (true full, with an initial backup)
• the log backup maintenance tasks are suspended for the duration of the test

Then:

• write down the current database LSN
• run your 3rd party project create
• check the newly added log information with select * from ::fn_log(oldcurrentLSN, NULL);

All write operations will apear in the log. From the physical operation (allocation unit ID) you can get to the logical operation (object id).

Now that being said, you should probably have a decent understanding of the 3rd party schema and data model if you plan to interact with it straight at the database level. If you are planning to update the 3rd party tool and you don't even know what tables to update, you'll more than likely end up corrupting its data.

Answer 2

I can think of the following ways you can track changes

1. Run SQL Server Profiler which will capture all queries that run on the server. You can filter these by database, schema or a set of tables, etc.
2. Use a 3rd party Transaction Log reader. This is very much a less intrusive process. You have to ensure that you are set to FULL recovery on the database.