How to tell which tables have changed/new rows 1

[jenlion]

Is there a query I can use to find out when a table last had data modified?

I have a DB in which we discovered users entered very bad data on Friday. We want to restore to before this data was entered.

We know that some work was done over the weekend to other tables, but there are thousands of tables. I am looking to see what other data may have been modified.

Is there some link to some sys-type table that tells you when data was last modified per table??

Thanks!

 

[ptheriault]

The short answer to your question is no. You can not see which data has been changed. However, If your database is set to FULL recovery and you still have the Transaction log from the date of the changes, then the answer is yes. You will need to purchase a tool to read your log. I know of two. I use Log Explorer by Lumigent. Red Gate also makes a log reader tool named SQL Log Rescue. Either one of these tools will allow you to undo the transaction that entered the bad data.
However...if your database is set to simple recovery then most likely those transactions are lost.

 

[jenlion]

P, Thanks.

In this case we'll just live; the DB isn't "live" yet so since I can't get a definite yes/no answer very quickly (we're only backing up occasionally in simple because we're still prepping the DB), we took a poll. There's still several people contributing but I think/hope we've got everything they did in the meantime.

Appreciate the straight quick answer!!!