8

I have Instant File Initialization enabled on our SQL Server, so the 'empty' part of the database file is not zeroed before being allocated. My understanding is that this means that the file could contain 'deleted' data.

So now I want to send a copy of a database (probably a backup file) outside of the company. But there's all that potentially sensitive 'deleted' data sitting around inside the file. Now I would like to zero the unused portion of the file.

Is that possible? I imagine I could create a new database and copy everything over, or perhaps restore a copy of the database to another server without Instant File Initialization enabled and then be aggressive with a ShrinkFile command to remove most or all of the unused portion of the database file, but is there a less manual and time consuming method? Ideally a command to tell SQL to zero the file as it would have done if Instant File Initialization was not enabled.

  • For general reference, instant data file initialization can be temporarily disabled without messing around with the service account permissions by using trace flag 1806 as Paul Randal blogged about here. – Jon Seigel Aug 22 '13 at 16:16
5

A SQL Server BACKUP only backs up extents that are being used to hold data. The unused extents are left behind by the backup. When a page is used for data it will be formatted for use as needed, so that page would be free of old data.

Therefore, all you should need to do is backup the database and restore it elsewhere. The restored files will be of the same size as the original database, but the unused extents will be created using the capabilities of the target server. This may be initialized fully or instantly initialized using the blocks of disk on the target server.

However, because extents are the level at which backups happen the unused pages in the extent could still have potential to expose some data when restored on another server. Not as much as could be exposed on the source server, since the unused extents are not restored.

  • 1
    Unused extents actually. Theoretically even some of the used extents can still contain space with 'old data', which is meaningless dribble for SQLServer but might be a treasure trove for a competitor armed with a hex editor and lots of spare time. If your data is that sensitive though, wouldn't you be zeroing all files anyway? – John Alan Aug 21 '13 at 15:44
  • @JohnAlan - Modified to acknowledge the exposure within extents. – RLF Aug 21 '13 at 20:27
  • The data isn't exactly GCHQ stuff - it's just basic customer data and the like, so it's unlikely that anybody is going to go to too much effort to retrieve it. I hadn't realised that backups worked like that, although it makes sense that it should. If there's no other quick and simple solution then that will probably do. – paulH Aug 22 '13 at 8:50
1

Have you considered restoring a copy of it, enabling TDE which will force it to 0 out, then disabling TDE again? I haven't tested this but I think it'll work, we'll have to test it out though.

Something else too, have you considered restoring a copy of it with IFI disabled, creating a new filegroup with new files, presizing it, rebuilding the clustered and other indexes on the new filegroup, then dropping the existing filegroup (If it is the primary filegroup you'll have to shrink it since you can't drop primary)

  • Using TDE sounds like an interesting approach, though I'm not sure whether it will remove any more unwanted data than the backup and restore method. If anybody knows more information about that then I'd be interested in knowing. – paulH Aug 22 '13 at 8:56

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.