Read backup file for particular transaction in SQL Server
Now we are entering into more tougher part of understanding the SQL Server.In our previous link we found that SQL Server backup files are readable but to some extent for getting the idea on what is actually going on.
http://manage-sqlserver.blogspot.in/2012/08/read-backup-file-of-sql-server.html
Now, am looking into the backup file for a particular transaction
In the above table room, there are 7 transactions.
Am gonna delete the transactions whose id is greater than 5, and table will be left with only 5 records.
I took the transaction log backup after that and try to read it to find out the deleted transaction details done by SPID 53.
Transaction log backup performed as
backup log room
to disk='C:\Users\sarmadh\Documents\SQL-Backups\room_log.trn'
Run the below query to find out the transaction “delete” in
the transaction log backup file.
SELECT
[SPID],
[Current LSN],
[Operation],
[Context],
[Transaction Name],
[Transaction ID],
[Transaction SID],
[Log Record Length],
[Description],
[Begin Time],
[End Time]
FROM
fn_dump_dblog (
NULL, NULL, 'DISK', 1, 'C:\Users\sarmadh\Documents\SQL-Backups\room_log.trn',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
where
[Transaction Name] like '%delete%'
Above query will give the output when delete happens.
Note one important column out of them is
1. Transaction ID
Filter the query again by keeping [Transaction ID] column as
mentioned in below query
SELECT
[SPID],
[Current LSN],
[Operation],
[Context],
[Transaction Name],
[Transaction ID],
[Transaction SID],
[Log Record Length],
[Description],
[Begin Time],
[End Time]
FROM
fn_dump_dblog (
NULL, NULL, 'DISK', 1, 'C:\Users\sarmadh\Documents\SQL-Backups\room_log.trn',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
where
[Transaction ID] ='0000:00000229'
Output will now give the complete transaction details happen
on that particular transaction id.
SPID who started the transaction, Transaction Name,
Operation performed, being time of transaction, End time of transaction and
last but not the least LSN NUMBER.
So, in the above way we can troubleshoot the Log file for
information needed.
Hope the above information helps
Thanks
