53

I need to view the transaction logs of a database on SQL Server 2008 in order to find a delete transaction and hopefully roll it back.

Unfortunately I have no clue where to start, and I'm finding it difficult to determine which are good articles on Google.

What should I do?

Peter Mortensen
  • 30,030
  • 21
  • 100
  • 124
109221793
  • 16,027
  • 37
  • 105
  • 158
  • Related: http://dba.stackexchange.com/questions/31348/sql-server-contents-of-transaction-log-file-in-more-detail – Vadzim Apr 26 '15 at 21:48
  • Read this blog and know how to use fn_dblog() function to find deleted records in SQL Server http://www.sqlserverlogexplorer.com/reading-sql-server-transaction-logs/ – Jason Clark Nov 17 '15 at 12:27
  • https://www.sqlshack.com/how-to-read-a-sql-server-transaction-log/ – Shiwangini Nov 16 '19 at 15:31

3 Answers3

52

You could use the undocumented

DBCC LOG(databasename, typeofoutput)

where typeofoutput:

0: Return only the minimum of information for each operation -- the operation, its context and the transaction ID. (Default)
1: As 0, but also retrieve any flags and the log record length.
2: As 1, but also retrieve the object name, index name, page ID and slot ID.
3: Full informational dump of each operation.
4: As 3 but includes a hex dump of the current transaction log row.

For example, DBCC LOG(database, 1)

You could also try fn_dblog.

For rolling back a transaction using the transaction log I would take a look at Stack Overflow post Rollback transaction using transaction log.

Community
  • 1
  • 1
kevchadders
  • 8,245
  • 4
  • 41
  • 61
20

You can't read the transaction log file easily because that's not properly documented. There are basically two ways to do this. Using undocumented or semi-documented database functions or using third-party tools.

Note: This only makes sense if your database is in full recovery mode.

SQL Functions:

DBCC LOG and fn_dblog - more details here and here.

Third-party tools:

Toad for SQL Server and ApexSQL Log.

You can also check out several other topics where this was discussed:

Community
  • 1
  • 1
JdMR
  • 1,260
  • 14
  • 9
5

I accidentally deleted a whole bunch of data in the wrong environment and this post was one of the first ones I found.

Because I was simultaneously panicking and searching for a solution, I went for the first thing I saw - ApexSQL Logs, which was $2000 which was an acceptable cost.

However, I've since found out that Toad for Sql Server can generate undo scripts from transaction logs and it is only $655.

Lastly, found an even cheaper option SysToolsGroup Log Analyzer and it is only $300.

Regular Jo
  • 4,552
  • 3
  • 19
  • 37
viggity
  • 14,789
  • 7
  • 83
  • 94
  • 1
    `SysToolsGroup Log Analyzer` costs $300 for a personal license and you arent allowed to use it for commercial purpose for that you need a Business license and that costs $799. – Peter Sep 26 '16 at 10:36