SQLBackupRestore.com - Inside the transaction log

There's certainly a wealth of information that's stored in the transaction logs. While SQL Server does not come with tools to analyze the logs, there are third-party tools that are able to do so.

Red Gate Software offers a free tool named SQL Log Rescue, that supports only SQL Server 2000. ApexSQL and Lumigent offer products with more features, named ApexSQL Log and Log Explorer respectively. These two products support SQL Server 7, 2000 and 2005.

Here is a sample of the information you can extract from a transaction log:

Viewing an active transaction log using ApexSQL Log

Here's what you can do with all that information:

find out exactly who (the user name as recorded in the log) did what (the change as recorded in the log) when (the transaction time) from where (if using ApexSQL Log) using which application (again if using ApexSQL Log)

undo changes to data, as each log record stores both the before and after values arising from each operation

Document history

7/11/2008

Initial release.