CODEWITHSUNDEEP
sql-serverwindowstransactions
sujith
sujith

Reputation: 29

How to read SQL Server transaction log

Is there any way to read the transaction log without third party tools.If yes kindly suggest the programming language to do this to get all the data changes in a table or file from a transaction log.Since I am using SQL Server 2012 standard edition I cant enable CDC for this purpose.

Kindly suggest

Upvotes: 2

Views: 2485

Answers (2)

Bonggal Siahaan
Bonggal Siahaan

Reputation: 31

Try this,


    Select 
        b.Description,
        d.AllocUnitName,
        b.[Transaction ID],
        d.name,
        d.Operation,
        b.[Transaction Name],
        b.[Begin Time],
        c.[End Time]
    from (
        Select 
            Description,
            [Transaction Name],
            Operation,
            [Transaction ID],
            [Begin Time]
        FROM sys.fn_dblog(NULL,NULL) 
        where Operation like 'LOP_begin_XACT'
    ) as b
    inner join (
        Select 
            Operation,
            [Transaction ID],
            [End Time]
        FROM sys.fn_dblog(NULL,NULL)
        where Operation like 'LOP_commit_XACT'
    ) as c
    on c.[Transaction ID] = b.[Transaction ID]
    inner join (
        select 
            x.AllocUnitName,
            x.Operation,
            x.[Transaction ID],
            z.name
        FROM sys.fn_dblog(NULL,NULL) x
        inner join sys.partitions y
        on x.PartitionId = y.partition_id
        inner join sys.objects z
        on z.object_id = y.object_id
        where z.type != 'S'
    )as d
    on d.[Transaction ID] = b.[Transaction ID]
    order by b.[Begin Time] ASC

Upvotes: 1

SAS
SAS

Reputation: 4045

Use fn_dblog:

select [Current LSN],
       [Operation],
       [Transaction Name],
       [Transaction ID],
       [Transaction SID],
       [SPID],
       [Begin Time]
FROM   fn_dblog(null,null)

To extract the data, there is info here (a lot of code will be required): https://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

Edit: as pointed out in the comments, link has code for delete only (fileters on LOP_DELETE_ROWS. To add insert & update edit like below - You would need to modify the Operation-clause like this:

 SELECT *
 FROM sys.fn_dblog(NULL,NULL)
 WHERE Operation IN ('LOP_DELETE_ROWS', 'LOP_INSERT_ROWS','LOP_MODIFY_ROW')

Please let us know your results!

Upvotes: 0

Related Questions