none
How to read SQL statements from Transaction log file.

    Question

  • We have deleted few of the records from one table. Now how can i collect those delete statements from the Transaction Log file which was used to delete those records. Do i need to use any tool or do we have any sql statement to get it.

    Monday, July 28, 2008 11:00 AM

Answers

  • Sanjiv,

     

    3rd party log readers are available for sql server 2005 transaction log viewing.  Pretty difficult to do it otherwise.
    Monday, July 28, 2008 11:38 AM
  • You can use this tool which allows information to explore your database:
     

    http://www.red-gate.com/products/SQL_Log_Rescue/index.htm

     

    PercyReyes,

    MCITP DBA, MCTS SQL Server, MCP

    Monday, July 28, 2008 4:48 PM
  • As others have mentioned, only 3rd party tools can read through the transaction logs.

    If you don't have access to those tools and cannot get an evaluation version to try out, you can backup your database and log. Then restore the database and log files using the STOPAT  option. This assumes that 1) you know the time/date that the rows were deleted and can stop the restore just before that activity took place, and 2) that you are willing to lose all changes since that time.

    Obviously this is a fairly drastic measure that you shouldn't enter into light-heartedly. The better bet is the 3rd party tools if you can get them.

    HTH...

    Joe

    Wednesday, July 30, 2008 1:27 PM
  • I have recovered from unbounded updates by doing what Joe offered above, only I restored to a new database copy, and then wrote a update statement that joined the tables across databases together to fix my broken production database.

     

    EDIT:

     

    I provided links to multiple tools on the following post:

    http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3673000&SiteID=1

    Thursday, July 31, 2008 1:25 AM

All replies

  • Sanjiv,

     

    3rd party log readers are available for sql server 2005 transaction log viewing.  Pretty difficult to do it otherwise.
    Monday, July 28, 2008 11:38 AM
  • You can use this tool which allows information to explore your database:
     

    http://www.red-gate.com/products/SQL_Log_Rescue/index.htm

     

    PercyReyes,

    MCITP DBA, MCTS SQL Server, MCP

    Monday, July 28, 2008 4:48 PM
  • As others have mentioned, only 3rd party tools can read through the transaction logs.

    If you don't have access to those tools and cannot get an evaluation version to try out, you can backup your database and log. Then restore the database and log files using the STOPAT  option. This assumes that 1) you know the time/date that the rows were deleted and can stop the restore just before that activity took place, and 2) that you are willing to lose all changes since that time.

    Obviously this is a fairly drastic measure that you shouldn't enter into light-heartedly. The better bet is the 3rd party tools if you can get them.

    HTH...

    Joe

    Wednesday, July 30, 2008 1:27 PM
  • I have recovered from unbounded updates by doing what Joe offered above, only I restored to a new database copy, and then wrote a update statement that joined the tables across databases together to fix my broken production database.

     

    EDIT:

     

    I provided links to multiple tools on the following post:

    http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3673000&SiteID=1

    Thursday, July 31, 2008 1:25 AM
  • I strongly recommend using third party tools for this, because transaction log isn’t human readable.

    Beside already mentioned tools, you can try transaction log readers like ApexSQL Log, or Toad for SQL Server by Quest
    Thursday, August 01, 2013 10:47 AM