How to read .TRN file data?

Question

I restored two databases using two different .BAK files (different nightly backup files)

I have a row of data that disappeared from the latest restored .BAK.

Need to find out why.

Is there a way for me to read/go through the .TRN data to see what user-action might have caused the issue?

Solution 1

Check out Apexsql ,they provide tools to read the transaction log. Its not freeware however.

There is also a undocumented feature inside SQl Server. See This Post for more details.

DBCC LOG(databasename, typeofoutput)

Solution 2

Paul Randal wrote on using an undocumented function to find out who dropped a table using the transaction log, you might be able to use the same concept.

In his post he was looking for a dropped table so I played with it on my local system and found you would filter for WHERE [Tranaction Name] = 'DELETE', for deleting a record from a table.

So this query:

SELECT [Current LSN], [Begin Time], SPID, [Database Name], [Transaction Begin], [Transaction ID], [Transaction Name], [Transaction SID], Context, Operation
FROM ::fn_dblog (null, null)
WHERE [Transaction Name] = 'DELETE'
GO

Returns this output

Current LSN Begin Time  SPID    Database Name   Transaction Begin   Transaction ID  Transaction Name    Transaction SID Context Operation
00000474:00000239:0001  2012/03/06 10:09:19:547 58  NULL    NULL    0001:000a67be   DELETE  0x010500000000000515000000628ADB6E31CC6098F269B2B9F8060000  LCX_NULL    LOP_BEGIN_XACT

Comments

[Marc]

stackoverflow.com/questions/4507509/…