Reputation: 29
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: 2463
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
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