3

After dropping a column with sensitive data from a SQL Server table, the data still exists on the data and index pages. To ensure the protection of this data, it needs to be removed from the pages as well.

For SQL Server (in particular, SQL Server 2012), what is an effective and efficient way to remove all of the data from the actual pages?

If the solution causes data to be moved to new/recycled pages, do the all the original pages get overwritten? For pages that are recycled for rebuilding, this would obviously be the case. For pages that were part of the table/indexes will they be wiped or have the sensitive data removed in some other manner?

|improve this question
  • Don't forget that your old backups still have the data. – vonPryz May 20 '14 at 12:52
  • Backups - excellent point for the overall class of problem! I'm not including it in the scope of this particular question for simplicity. – BrianCooksey May 20 '14 at 13:08
  • Related question: dba.stackexchange.com/questions/11280/… – BrianCooksey May 23 '14 at 16:42
2

No, sorry, there is no feature for reinitializing empty pages or extents when they are unused.

It is true that if you take a backup and create a new database from that backup the empty extents are not backed up, so that data could not be restored to the new database. But that still leaves some old data in the extents that are still in use. See:

'Cleanse' a SQL Server database file created with Instant File Initialization enabled?

I suppose that overwriting the data first, then deleting it, might help. But that may not be practical for many reasons. (Too much data to update, constraints, unique indexes, triggers, etc.)

|improve this answer
  • So, to be clear: an in-place backup and restore should remove any sensitive data from unused extents. That would only leave the problem of removing the data from the active extents. So any solution that cleans the active extents could be followed by an in-place backup and restore cycle to ensure complete cleansing. Correct? – BrianCooksey May 20 '14 at 13:16
  • 1
    If you backup and restore you should restore to a new database, since a RESTORE...REPLACE would (as I understand it) retain the existing 'empty' extents. And you should have Instant File Initialization disabled. – RLF May 20 '14 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.