How to view transaction logs in SQL Server 2008
Sql ServerTransaction LogSql Server Problem Overview
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?
Sql Server Solutions
Solution 1 - Sql Server
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.
Solution 2 - Sql Server
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:
Solution 3 - Sql Server
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][1], which was $2000 which was an acceptable cost.
However, I've since found out that [Toad for Sql Server][2] can generate undo scripts from transaction logs and it is only $655.
Lastly, found an even cheaper option [SysToolsGroup Log Analyzer][3] and it is only $300.
[1]: http://www.apexsql.com/sql_tools_log.aspx "ApexSQL Logs" [2]: http://software.dell.com/products/toad-for-sql-server/ [3]: http://www.systoolsgroup.com/sql-log-analyzer.html