How to get SQL statement from TRANSACTION LOG BACKUP FILE? [closed]

Question

I just want to ask regarding transaction logs in SQL Server. We can take backup of those log files in .bak format at our any system location.

The problem is to extract SQL statement/query from transaction log backup file. We can do it using fn_dump_dblog function. But what we want is to extract the query or data on which transaction has to be done in logs.

I want to do it manually same as "apex" tool do for sql server. And don't want to use any third party tool.

Right now I am able to extract table name and operation type from logs. But still searching for SQL statement extraction.

Answer 1

Decoding the contents of the transaction log is exceptionally tricky - there is a reason Apex gets to charge money for the tool that does it - it's a lot of work to get it right.

The transaction log itself is a record of the changes that occurred, not a record of what the query was that was executed to make the change. In your question you mention extracting the query - that is not possible, only the data change can be extracted.

For simple insert / delete transactions it is possible to decode them, but the complexity of just doing that is too large to reproduce here in detail. The simpler scenario of just decoding the log using fn_dblog it analogous, but the complexity of that should give you an idea of how difficult it is. You can extract the operation type + the hex data in the RowLogContents - depending on the type of operation, the RowLogContents can be 'relatively' simple to decode, since it is the same format as a row at a binary / hex level on the page.

I am loathe to use a link as an example / answer, but the length of the explanation just for a simple scenario is non-trivial. My only redemption for the link answer is that it is my article - so that's also the disclaimer. The length and complexity really makes this question un-answerable 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/

There have been further articles published which built on this to try automate this logic into t-sql itself.

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

The time / effort you will spend attempting to write your own decoding is sufficiently high that compared to the cost of a license, I would never recommend attempting to write your own software to do this unless you planned on selling it.

You may also wish to consider alternative tracing mechanisms being placed in-line with the running of the application code, and not something you try reverse engineer out of a backup.