patternsqlMinor
What is an effective way to wipe sensitive data on a data pages after dropping a table's column?
Viewed 0 times
aftersensitivewhatcolumnwipedroppingwaypageseffectivedata
Problem
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?
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?
Solution
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.)
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.)
Context
StackExchange Database Administrators Q#65469, answer score: 2
Revisions (0)
No revisions yet.