SQl Query to advise of record deletion

[JohnOB]

Is it possible to write a query which will tell you which records have been delted from a table, on a particular date (or between dates)

Thanks

"The only stupid question is the one that doesn't get asked

 

[ptheriault]

No, the delete would be recorded in the transaction log and you could recreate the delete depending on how far back you keep copies of your TLog.
However you could Create a trigger on the table to select the data being deleted from the deleted table. Then take the deleted data and insert it into an archive table. (maybe in another database.)

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Really, the only way to do such a thing is to use a third party tool to read your transaction logs (Apex or Lumigent sell them) or to set up some sort of auditing which records DELETES/UPDATES/INSERTS as they happen (from the deleted and inserted tables). Then you could query off of those.

You cannot, however, query on stuff that is past unless, like Paul said, you've kept your logs and have a tool which can read them.

Additionally, if you're looking for a certain time frame and you have an older copy of your database, you could use Red Gate's SQL Data Compare tool to find the differences. I believe you can tell it to look for what's in the older DB which isn't in the newer DB, but I haven't played with the tool that much.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"