There was an update statement which was ran in our production environment which should not have been run.

I wanted to know if there was a way I can find when and who ran that specific update statement. I no longer have the corresponding .trn files but I do have the .mdf and .ldf file from that specific day.

Is it possible to find this without the .trn files?


The short answer is maybe. You can read the SQL Server transaction log (that is your .LDF file) in an online database. Using the fn_dblog function. This post describes that process.

There is a slightly longer answer, though:

Why/how do you have your .mdf and .ldf from that day? If you had a crash consistent copy of them - in other words, a Volume Snapshot Service (VSS) orchestrated snapshot of the files with a tool that is SQL Server aware - then you are in good luck. If the snapshot was just taken randomly with some other tool that doesn't instruct SQL Server to freeze/thaw the IO before and after the snapshot there is a chance that your log file and data file are not 100% consistent (as in from the exact same moment in time) and it won't be of any use.

The steps you could go down are:

  1. Attach that MDF and LDF (note since you want to read the log file, you can't do an attach rebuild log - you need to attach both so see the note above). Attach them as a new Database, lest you overwrite the existing database entirely and then someone comes after you with a question that says "how do I find out who overwrote the production database while trying to find out who dropped a table" it could get messy then.
  2. Look to the link I shared above and start seeing what you can see in the log file.

You can also do things like - figure out who has permissions. Honestly, as a DBA, when an object gets dropped there is a shared responsibility there. The person who did the drop, and the person who let the person who did the drop do the drop. When I'm on dad duty, and one of my kids does something a little silly that mom would never allow when on mom duty, we're both in trouble when mom comes home. They shouldn't have done that, but I shouldn't have been answering questions on DBA.SE and I should have been controlling their access to whatever mess they caused. It's sort of like that. So this is a good reminder about least privilege.

| improve this answer | |
  • We were able to pull the .mdf/.ldf from a VM backup done by Veeam from that day. – davszur Oct 17 '17 at 22:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.