1

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?

Alex
  • 1,663
  • 6
  • 23
  • 32
  • http://stackoverflow.com/questions/4507509/how-to-view-transaction-logs-in-sql-server-2008 – Marc Apr 05 '16 at 17:56

2 Answers2

4

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)
Community
  • 1
  • 1
Alex
  • 7,901
  • 1
  • 41
  • 56
3

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