Data Removal Real?

I have a situation where my client wants to ensure that data which is removed from a SQL Server database is truly removed. That is, if I delete a record, or delete data from a row, can I be sure that the data is truly gone and that it's not just the internal B-Tree pointers that were removed? Can I trust that someone with access to the .mdb file could not perform some binary pattern matching on the file and find the data that I assumed was deleted (regardless of how difficult that task would be)? I'm using SQL Server 2000. Thanks in advance.

The data is deleted.

However, depending upon your Recovery model, it may be 'hanging' around in the TRANSACTION LOG file for awhile, and anyone with access (and permissions) to the TRANSACTION LOG file, and one of several third party tools, may be able to inspect the deleted data.

(It's best, before they show up with the subpoenas, to backup and restore - and delete any saved backups and transaction logs, and the use a DOD approved sector wiping utility and burn the tapes... But of course, that is not a good thing to be caught having done...)

|||

Ok, thanks. So, I should look for a recovery model that doesn't use logging? This implies no recoverable transactions, but that would be ok.

(Side note, I'm not worried about subpoenas. Foreign governments are the perceived risk.)

|||

Simple recovery model has the shortest logging latency.

However, that means no data recovery since the last FULL BACKUP.

It seems that you may need to better secure the server ...

|||

I wasn’t sure about this one, so I asked the experts and this is what they replied back:

all the deletes are only logical … The only way to get rid of the data for sure is to DTS to a new (clean) database.

They mentioned some SQL Server 2005 SP2 feature for Common Criteria (CC) that is used to clear the information on the pages, but again, they mentioned it is only a logical deletion.

Arnie, it may be possible that you were talking about this feature, can you please confirm? Unfortunately I have limited knowledge on this particular area myself.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine