How to get SQL statement from transaction log transaction log file?

Question

I just want to ask about transaction logs in SQL Server. We can backup these log files in .bak format anywhere in the system.

The problem is retrieving the SQL query / query from the transaction log backup file. We can do this with the fn_dump_dblog function. But we want to retrieve the query or data on which the transaction should be performed in the logs.

I want to do this manually just like the apex tool for sql server. And do not want to use a third-party tool.

Now I can extract the table name and operation type from the logs. But still looking for SQL query retrieval.

Answer 1

I found a good tool that contains a function that matches your problem. SSMS toolkit . Go to have a try.

Answer 2

Deciphering the contents of a transaction log is extremely complex - there is a reason Apex gets paid for the tool that does it - it's a lot of work to figure it out.

The transaction log itself is a record of the changes that occurred, and not a record of what was performed to modify the request. In your question you mention query retrieval - this is not possible, only data can be retrieved.

For simple insert / delete operations, they can be decoded, but the complexity is simply too great to reproduce in detail here. A simpler scenario of simply decoding a log using fn_dblog is similar, but the complexity of this should give you an idea of ​​how complicated it is. You can extract the operation type + hexadecimal data in RowLogContents - depending on the type of operation, RowLogContents can be “relatively” simple to decode, since this is the same format as a binary / hexadecimal string on a page.

I do not like to use the link as an example / answer, but the length of the explanation is simply not trivial for a simple scenario. My only atonement for responding to the link is that this is my article - so is the disclaimer. The length and complexity really make this question not answer the answer with a positive answer!

https://sqlfascination.com/2010/02/03/how-do-you-decode-a-simple-entry-in-the-transaction-log-part-1/ https://sqlfascination.com/2010/ 02/05 / how-do-you-decode-a-simple-entry-in-the-transaction-log-part-2 /

Other articles have been published that built on this to try to automate this logic in t-sql itself.

https://raresql.com/2012/02/01/how-to-recover-modified-records-from-sql-server-part-1/

The time / effort that you spend trying to write your own decoding is high enough that, compared to the cost of a license, I would never recommend writing your own software to do this if you do not plan to sell it.

You might also want to consider alternative tracing mechanisms related to running application code, rather than trying to reverse engineer from backup.