a friend asked me if there is a way to see the past dml statements and I wasn't really sure on how to go about answering that question. What he wants to see is the last set of insert statements. So that means it could be more than 1 record. At first I was just saying to check the latest identity, but then he asked what if more inserts were performed at the same time. Can you guys help me out? Is there a DMV I should use that I just don't know about? Thanks.
Asked
Active
Viewed 72 times
0
-
Is he looking for [Change Tracking](http://msdn.microsoft.com/en-us/library/cc280462(v=sql.105).aspx) and/or [Change Data Capture](http://msdn.microsoft.com/en-us/library/bb522489(v=sql.105).aspx)? – Remus Rusanu May 06 '13 at 21:06
-
It would help to explain *why* he wants to see the previous inserts. Is it for auditing; to be able to make the same inserts in a second database; to roll them back or for some other purpose? Each scenario may have a different answer. – Pondlife May 06 '13 at 21:18
1 Answers
0
If you did not prepare for this question then there is no build in way to get to that information. However, you could use third party log reader tools to recover (all) the last statements that where executed against the database. This requires the database to be in Full recovery mode. You could potentially go back as far as you have log backups with this method.
If you want to prepare for that question being ask in the future, you have several options. The most obvious one is Change Data Capture. You also could write a trigger yourself that records data changes. You could also run a trace capturing SQL Batch Started. Finally you could use a third party network sniffer/logger to capture all statements send to the server (this however requires that connection encryption is not used).
Sebastian Meine
- 11,260
- 29
- 41