Home / server / Questions / 146188
Accepted
Danilo Brambilla
Asked: 2010-05-29 01:53:40 +0800 CST

SQL Server: proof of sensitive data really deleted by table drop

  • 772

I was asked to drop some tables on a Company SQL 2005 Server and provide proof of deletion because of these tables contains sensitive data. Is it possible?

Thank you all for help

sql-server-2005

6 Answers

  1. No - especialy as a drop does NOT DELETE THE DATA at this moment.

    • 3

  2. If you're just looking to prove the drop, you can use this:

    USE MyDB
SELECT Name
FROM sysobjects
WHERE Name='MyDroppedTableName'

    If nothing is returned then the drop worked. But as the others have said, the drop of the table just removes the pointer to the data.

    And on another note, if you're looking for total eradication you also have to keep backups in mind as well. If your database has ever been backed up then that data is there, and if the server's ever been backed up you have it those as well.

    • 3

  3. If they're looking to totally eradicate the data, no; and the only way to prove it would be remove the disks containing the data and scan it outside of the file system. It would be better to replace the sensitive data with random characaters. Even then you have to deal with the old data residing in transaction logs.

    • 1

  4. The only way to truly destroy all traces of the data is to copy the good data into a new database, and perform a secure deletion of the old database's files. You'll then need to securely delete any backups of the DB and server that may contain the target data. It's a task that leaves you open to loosing data if you should ever need one of those backups.

    • 1
  5. Best Answer

    As suggested above we did a secure delete of database files using sdelete (sysinternals), then we removed the disks and sent them to the company (they compared the disk serials from the vendor hardware monitoring tool). Backup tapes were sent too.

    • 1

  6. You can update the data, replace it with spaces, before drop. This way you are replacing the value bits on the disk with space values. However, a pre-update image of the data will always be in the log and in backups. The actual propagation of erasure would have to be custom tailored for your environment, according to whatever technologies and procedures are in place (eg. log-shipping and/or mirroring, database snapshots, SSIS extraction jobs, track the location of backups etc).

    • 0