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:
- 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.).