Let me start off saying I am not a DBA.
My company is in the process of moving our SQL Server 2012 databases (recovery model of full) from one network to another.
Due to security requirements, we must transfer the .MDF and not backups. We do not transfer any .LDF files. We also need to perform various update statements to "scrub" data that we deem sensitive. We then execute a full database backup.
After performing these steps, we must take the .MDF and process it through a file scanner to parse the file and find any words from a black list. Now these update statements should have taken care of these words, but the file scanner finds them still.
It appears that the only way to fully "scrub" this data is to move the data into a temp table, drop and recreate the original table and re-insert the data back into the newly recreated original table. This would involve dropping and recreating every constraint, index, and trigger each table may have.
There has to be a better way than this. What are we perhaps doing wrong?
For clarity, the overall process sequence is:
- scrubbing
- checkpoint
- backup
- detach
- filescan mdf
We don't use TDE because it is not the transferring that needs to be secure. there is data that cannot exist on the new network (security requirements of govt. customers).
The blacklisted terms exist just in tables (not in stored procedures, functions, triggers etc.)
The file scanner is third party, so I don't know the internal mechanisms. I know the scanner can scan various different files (text, Excel, PDF, etc.).
Best Answer
This is probably one of the silliest processes I've ever heard of, but anyway.
After you've updated your data to remove the offensive words, run a checkpoint, and then restarted SQL Server (just to make sure it's all committed), the only way to guarantee that the underlying disk has been overwritten, and the "offensive" data expunged, is to perform a shrink of the database file.
This effectively takes the data pages from the end of the file and inserts them into open spaces at the beginning of the file to fill up "empty" space. It's an horrific process for SQL Server, as it causes massive fragmentation, but it will "solve" your problem.
Once you've moved your data to the new server, you must run a full index rebuild to defragment the tables, but, hey, it should work.