Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Geremore
Starting Member
2 Posts |
 Posted - 2011-08-22 : 06:25:06
|
| Hi,Got a historical database with delicate information. At any time, I want to be able to check that the data in the database is unchanged. The database is huge, more than 1 billon records, more than 1K tables. Tried to compare checksums of different dumps (backups), which doesn't work. Is there another way?Thanks in advance for any help.Geremore |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2011-08-22 : 08:58:02
|
| Make the database READ-ONLY.Make a Golden-Copy onto a CD and re-install same on a nightly basis. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-22 : 11:23:17
|
| Just in case you need a broader solution:You can have UpdateDate columns in tables with a TRIGGER which will set the UpdateDate column on each insert/update. SELECT MAX(UpdateDate) FROM MyTable will then tell you when it was last changed. This won't help with deleted records.You could create an audit table, and a trigger, that logs changes to the Audit table. This will include deletions. Then a query of AuditTable will tell you what has changed (since date X).You could have a trigger that explicitly disallows INSERT/DELETE/UPDATE. Then users can only select. You can control permissions to only allow SELECTs, but the Trigger would be a LongStop in case something with SysAdmin permissions accidentally did an Update.But for a straightforward fix using Andrew's suggestions. |
|
|
|
Geremore
Starting Member
2 Posts |
Posted - 2011-08-23 : 05:20:06
|
| Thanks everybody! The database is read-only (of course). The fact that someone finds out the administrator password, adjusts data, makes incorrect reports is a risk. Guess the best is randomly check the data reported.The scripting of all data (1 file per table) in order to generate a checksum fails: Cannot access properties or methods... because it has been dropped. |
|
|
|
|
|
|
Checksum database