December 20, 2016 at 6:37 am
I have a situation where somehow, an entire table of records were deleted from a database. I restored a backup and the records are back, so the problem there is resolved but management wants to know who did that. Nobody is copping to it, and the application doesn't log events (I think they should implement this, but I digress) so I restored the database to a point in time AFTER the delete(s) was done and tried the techniques found here:
https://www.mssqltips.com/sqlservertip/3090/how-to-find-user-who-ran-drop-or-delete-statements-on-your-sql-server-objects/[/url]
and here:
https://www.mssqltips.com/sqlservertip/3076/how-to-read-the-sql-server-database-transaction-log/[/url]
I tried first to just get a count of the number of "LOP_DELETE_ROW" operations using this code:
USE <MYDATABASENAME>;
GO
SELECT COUNT(*)
FROM fn_dblog(null,null)
WHERE [Operation] = 'LOP_DELETE_ROW'
The code ran for 4.5 hours and still hadn't returned anything (nor did it fail) so I cancelled it before I went home last night at that point. It cancelled instantaneously.
So my question is this; What (if anything) am I doing wrong here? I really need to find out what principal performed the delete, yet my workplace doesn't provide me any tool to do this and all I have are these Microsoft undocumented functions which don't seem to be working. Does anyone have any advice, pointers, a good site that explains what to do in depth or even an inexpensive log reading tool I can (hopefully) talk management into springing for? I am at a loss at the moment so anything you got is appreciated. I'm off now to peruse the SQL Server Central E-Book on Transaction Logs which I have to see if anything can be gleaned there. Help?
December 20, 2016 at 6:45 am
Siberian Khatru (12/20/2016)
so I restored the database to a point in time AFTER the delete(s) was done and tried the techniques found here
And there's the problem.
When you restore a DB from backup, SQL doesn't put all the log records that were in the log back into the log. It doesn't need to and it wouldn't be efficient to do so. A restored DB will only have log records generated by the restore, the recovery and anything that happened afterwards.
In short, you're not going to find out who deleted anything. The log records aren't there (unless you have a copy of the mdf and ldf from after the delete, before the database was restored), and you don't have any custom auditing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2016 at 7:13 am
GilaMonster (12/20/2016)
Siberian Khatru (12/20/2016)
so I restored the database to a point in time AFTER the delete(s) was done and tried the techniques found hereAnd there's the problem.
When you restore a DB from backup, SQL doesn't put all the log records that were in the log back into the log. It doesn't need to and it wouldn't be efficient to do so. A restored DB will only have log records generated by the restore, the recovery and anything that happened afterwards.
In short, you're not going to find out who deleted anything. The log records aren't there (unless you have a copy of the mdf and ldf from after the delete, before the database was restored), and you don't have any custom auditing.
Thanks for the reply Gail. I see what you're saying and after reading a bit of that E-Book you and Tony Davis so kindly wrote I can even catch a glimpse of why that is. So you are saying to me that if (and only if) I can find a backup of the actual mdf/ldf pair after the delete and presumably before the tran log is backed up -- that's the only way to find out what I seek? In our environment, we do full backups twice daily and log backups hourly -- does that make a difference? I mean, if the records were deleted at 11:23a and a tran log backup happened at 12p -- am I out of luck?
Thanks again for the help thus far!
December 20, 2016 at 7:29 am
Siberian Khatru (12/20/2016)
I can find a backup of the actual mdf/ldf pair after the delete and presumably before the tran log is backed up -- that's the only way to find out what I seek?
Yes.
Put this down as a learning exercise and, if management is concerned about future events, discuss adding custom auditing, of what form and where it'll be stored.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2016 at 7:38 am
You might be able to use fn_dump_dblog to read your log backups:
December 20, 2016 at 9:08 am
GilaMonster (12/20/2016)
Siberian Khatru (12/20/2016)
I can find a backup of the actual mdf/ldf pair after the delete and presumably before the tran log is backed up -- that's the only way to find out what I seek?Yes.
Put this down as a learning exercise and, if management is concerned about future events, discuss adding custom auditing, of what form and where it'll be stored.
Thanks Gail, I appreciate your expertise and help. I have so advised management (as I had in the past) so we'll see if this gets them moving in the right direction.
Merry Christmas (or Happy Holidays if you prefer) to you.
December 21, 2016 at 9:49 am
There is a free trial with ApexSQL logreader that may help (shows every 10th row).
I've used it with some success in the past but to get all of the results you'll need a license.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply