CODEWITHSUNDEEP
sqlsql-serversecurity
Alex Bagnolini
Alex Bagnolini

Reputation: 22412

Does SQL's DELETE statement truly delete data?

Story: today one of our customers asked us if all the data he deleted in the program was not recoverable.

Aside scheduled backups, we shrink the log file once a day, and we use the DELETE command to remove records inside our tables where needed.

Though, just for the sake of it, I opened the .mdf file with an editor (used PSPad), and searched for a particular unique piece of data -I was sure- was inside one of tables.

Problem: I tracked it in the file, then executed the DELETE command, and it was still there.

Question: Is there a particular command we are not aware of to delete the records physically form the disk?

Note: we know there are particular techniques to recover lost data from the hard drives, but here I am talking about a notepad-wannabe!

Upvotes: 4

Views: 6948

Answers (4)

meriton
meriton

Reputation: 70584

You could update the record with dummy values before issuing the delete, thereby overwriting the data on disk before the database marks it as free. (Whether this also works with LOB fields would warrant investigation, though).

And of course, you'd still have the problem of logs and backups, but I take it you already solved those.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294407

If your client is concerned about data security it should use Transparent Database Encryption. Even if you obliterate information from the table, the record is still in the log. Even when log is recycled, the info is still in the backups.

Upvotes: 4

cdonner
cdonner

Reputation: 37698

SQL Server just marks the space of deleted rows as available, but does not reorganize the database and does not zero out the freed up space. Try to "Shrink" the database, and the deleted rows should no longer be found.

Thanks, gbn, for your correction. A page is the allocation unit of the database, and shrinking a database only eliminates pages, but does not compact them. You'd have to delete all rows in a page in order to see them disappear after shrinking.

Upvotes: 4

gbn
gbn

Reputation: 432471

The text may still be there, but SQL Server has no concept of that data having any structure or being available.

The "freed space" is simply deallocated: not removed, compacted or zeroed.

The "Instant File Initialization" feature relies on this too (not zeroing the entire MDF file) and previous disk data is still available eben for a brand new database:

Because the deleted disk content is overwritten only as new data is written to the files, the deleted content might be accessed by an unauthorized principal.

Edit: To reclaim space:

ALTER INDEX...WITH REBUILD is the best way

DBCC SHRINKFILE using NOTRUNCATE can compact pages into gaps caused by deallocated pages, but won't reclaim space in a page for deleted row

Upvotes: 6

Related Questions