none
Transaction Logs RRS feed

  • Question

  • Is there a way to transform contents of transaction log file to sql statements.  Or, for a table, can I get 1) deleted rows and theirkeys  2) inserted rows and all there values 3) updated rows with key and new column values.

    Thanks in advance

    Wednesday, November 1, 2017 6:48 PM

All replies

  • Not sure what you're trying to accomplish but you can try and read the contents of log file with undocumented function fn_dblog:
    How to Read the SQL Server Database Transaction Log

    Outside of this, your options are going with a third-party log reader tool like Apex or Red Gate.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Wednesday, November 1, 2017 6:55 PM
  • Hi rgelfand,

    Based on your description, I assume you attempt to record insert, delete, and update activity that is applied to a SQL Server table, right? If so, I would recommend you use Change Data Capture(CDC), which read data from SQL Server transaction log and records in a separate table.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 2, 2017 4:37 AM
  • To Analyze the transactions of SQL server transaction log file, you can go through Fn_dblog() function but this will only provide you the option to check what transaction you have performed and to perform this you will have to follow a long approach as discussed in this article: https://www.mssqltips.com/sqlservertip/3076/how-to-read-the-sql-server-database-transaction-log/

    To better understand what transaction performed on certain timing, going through SQL Server Transaction Log Reader is the best approach to accomplish your need. 
    Thursday, November 2, 2017 6:01 AM
  • Is there a way to transform contents of transaction log file to sql statements.  Or, for a table, can I get 1) deleted rows and theirkeys  2) inserted rows and all there values 3) updated rows with key and new column values.

    Thanks in advance

    I would either suggest change data capture or trigger which has updated,inserted and deleted values. But no one is going to create statement for you, you might have to do that yourself

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, November 2, 2017 1:44 PM
    Moderator
  • Hi Hannah,

    Are you saying the CDC facility records, to a table, what was applied to the target database when transaction log is restored?

    Thanks 

    Thursday, November 2, 2017 4:26 PM
  • Hi rgelfand,

    When a table is enabled for change data capture, an associated capture instance is created to support the dissemination of the change data in the source table. The capture instance consists of a change table and up to two query functions. And a source table can have a maximum of two capture instances. For more information, you can refer to sys.sp_cdc_enable_table.

    Besides, I found the following scripts which allow you generate UNDO/REDO scripts based on CDC, click see if they help.

    SQL Change Data Capture – Automatically generate DML UNDO/REDO Scripts (Part 1)

    SQL Change Data Capture – Automatically generate DML UNDO/REDO Scripts (Part 2)

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Hannah Yu Tuesday, November 28, 2017 9:35 AM
    Wednesday, November 8, 2017 9:54 AM