SQL Server Performance Forum – Threads Archive

database timestamps

Hi, I need some way to detect if a database has been restored from an old backup. My application has sql server column with an incrementing value that must only be updated via my app never be set to a lower value. I have created table A with a timestamp column. After each row modification in this table I encrypt and save the timestamp value to a column in a second table B. Table B holds the encrypted timestamp values for each row in A. I can then compare the timestamp values in tables A and B to determine if anyone has changed the data. If someone modifies a row in A directly the timestamps will be out of sync. The problem is someone can still change the data back to an old value by inserting directly into the timestamp column using SELECT INTO, or by restoring the two database tables from an earlier backup. Does any one know a way of detecting if a database has been restored ? (maybe something in system tables or transaction log ?) Thanks.

---

This was a good question and I had no idea so I checked it out. The MSDB database just happens to have a table called "restorehistory" which is what you’ll be looking for.

---

thanks for the reply. yes i have been looking at the msdb tables which record backup and restore history. this is useful but not quite good enough either as a dba can easilt clear the backup/restore history.

---

]]>