patternsqlMinor
'Cleanse' a SQL Server database file created with Instant File Initialization enabled?
Viewed 0 times
fileenabledsqlcreatedwithdatabaseinstantserverinitializationcleanse
Problem
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.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#48470, answer score: 5
Revisions (0)
No revisions yet.