transaction log - How to undo a delete operation in SQL Server 2005?

Question

Our Test DB is suddenly missing rows. We want them back.

Is there a way to sift through everything that has happened to the database today? Each SQL statement? I presume this kind of stuff is in the transaction log, but am not sure how to view it.

Is there a way to undo delete operations?

BTW: Yes, we do have a backup, but would prefer to find the cause of the deletion as well...

Answer 1

[late answer but hopefully useful]

There is a way to recover deleted rows using transaction log but only if you use 3rd party tools and only of your database is in full recovery mode.

Dell (formerly Quest) has Toad for SQL Server and ApexSQL has ApexSQL Log and ApexSQL Recover that can also read t-log and recover data. Unfortunately Log Rescue from Red Gate can only read logs on SQL Server 2000.

There is also a way to read t-log using undocumented dbcc log command. See more details here.

Considering you already have database backups you can restore these in separate database and then use one of many data comparison tools that exist on the market to insert missing data into production database. Of course this can only recover data delete prior to creating a backup.