0

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

sujith
  • 9
  • 2
  • 1
    Possible duplicate of [Read SQL Server transaction log](https://stackoverflow.com/questions/9014531/read-sql-server-transaction-log) – SMor Aug 04 '17 at 11:59
  • it's a definite duplicate of this. https://dba.stackexchange.com/questions/182665/sql-servers-equivalent-in-mysqls-binary-log – sepupic Aug 04 '17 at 14:06

2 Answers2

1

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
Bonggal Siahaan
  • 31
  • 3
0

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!

SAS
  • 3,943
  • 2
  • 27
  • 48
  • but where is the data here? i need the exact data that have been changed – sujith Aug 04 '17 at 10:53
  • You could also look for a third party tool. – SAS Aug 04 '17 at 11:11
  • Log file stores the before and the after image of what was changed, it's not a whole row, they are parts that were changed. I already ansered you on dba.stackexchange, the log file is not supposed to be read by user and the form in which fn_dblog() returns modified data is not very friendly – sepupic Aug 04 '17 at 11:22
  • >>>but where is the data here<<< The code posted just does not select it. Use columns [RowLog Contents 0], [RowLog Contents 1], [RowLog Contents 2] to get the before and after images – sepupic Aug 04 '17 at 11:27
  • Yes but in order to get the actual data in a readable format, there is a lot of work needed.. probably better to look for third a party product. – SAS Aug 04 '17 at 11:30
  • I just found an artice with a full example of doing this! Adding link to my answer. – SAS Aug 04 '17 at 13:53
  • @SAS, the script you found elaborates only LOP_DELETE_ROWS rows of log file, so it's only good to recover deleted data. The OP asked for CHANGES, i.e. he wants info for UPDATE, the before and after images of changed data – sepupic Aug 04 '17 at 14:05
  • Thank you for pointing that out. I added some more code, there is still more work needed but I think it's the closest you will find to a full solution. – SAS Aug 07 '17 at 06:29