Restoring from full backup and transaction logs

Question

Restoring from full backup and transaction logs

sciggs

Valued Member

Points: 51
More actions
July 1, 2016 at 1:08 pm

#314795

All,
Please bear with me as I'm fairly new to SQL. 😀 I've got a scenario where certain data was deleted from a database. I am trying to use the transaction logs to determine what user ID deleted the data, etc....
I have restored the full backup and the transaction log backups to the point just after where the data was deleted. I am querying the transaction logs to try to find any delete statements but it's turning up blank. I would expect to be able to see something, but perhaps I don't understand transaction log backup completely.
For testing, I deleted a row from a table and then checked the transaction logs and I do see that delete row. What am I missing? It's likely I don't quite understand transaction log backup/restore entirely. I am not worried about recovering the data, as we have already handled that. I thought I would be able to restore and then look at these logs to determine how the data was deleted.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

rvsc48 SSCertifiable Points: 7686 More actions · Answer 1

Have you tried querying the transaction log backups (not the active transaction log) to get this information? Do you have a transaction log backup which contains the point in time when the data was deleted?

sciggs Valued Member Points: 51 More actions · Answer 2

Ahh. That sounds like the issue. I am querying current log. How would I go about querying the transaction log backup?

I have transaction log backup for each hour and I know from within which hour time frame that the data was deleted.

rvsc48 SSCertifiable Points: 7686 More actions · Answer 3

Try these queries run together as one, filling in the name of your transaction log backup file name and your location in place of what is there (in both queries):

WITH CTE

as

(SELECT [Transaction ID], count(*) as DeletedRows

FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'C:\SQLFiles\Backups\YourDatabase_LOG_20160701_150036.trn',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

WHERE Operation = ('LOP_DELETE_ROWS')

AND [PartitionId] = (SELECT sp.partition_id

FROM sys.objects so

INNER JOIN sys.partitions sp on so.object_id = sp.object_id

WHERE name = 'Location')

GROUP BY [Transaction ID]

)

SELECT [Current LSN], a.[Transaction ID], [Transaction Name], [Operation], [Begin Time], SUSER_SNAME([TRANSACTION SID]) as LoginName, DeletedRows

FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'C:\SQLFiles\Backups\YourDatabase_LOG_20160701_150036.trn',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as a

INNER JOIN cte on a.[Transaction ID] = cte.[Transaction ID]

WHERE Operation = ('LOP_BEGIN_XACT')

sciggs Valued Member Points: 51 More actions · Answer 4

Excellent!! That has gotten me down the path. I had to comment out the WHERE clause in the first part of the query to get it to return any results but have been able to tweak it enough where I can comb through the logs now.

Thanks much!