1

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

  • 1
    I could be way off base here, but could this be due to instant file initialization not zeroing out the data for growths? What is the scanner actually doing? Is it running SELECT queries? Or maybe DBCC PAGE? Or...? – Tara Kizer Oct 3 '16 at 21:28
  • So do you have IFI configured on the original instance? – Tara Kizer Oct 3 '16 at 21:32
  • 2
    Data can end up copied around the file for multiple reasons. E.g. A page split is one reason or maybe sensitive data was deleted and is still on the page as a ghosted row. Why does this need to be transferred as mdf rather than exporting out all good data then reimporting the other end? – Martin Smith Oct 4 '16 at 5:36
  • Can you explain why you are not also copying the LDF files? How are you making a copy of the MDF? – datagod Oct 18 '16 at 15:11
2

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.

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.