SQL Backup and Recovery - Cheating with DBCC FREEZE_IO

A question came in from the field with regards to obviating VDI/VSS for a quiesced SQL database SAN-based snapshot.

The question was:
Can someone leverage the undocumented (!) DBCC command FREEZE_IO (and it's counterpart THAW_IO) to achieve a reliable recovery point? Also, while the snapshot appears to work when presented elsewhere, they cannot apply Transaction Log backups to restore to a point in time. Is this expected?

My first thought was: congratulations on finding the undocumented/deprecated DBCC FREEZE_IO command.

My second thought was: do not run this on a production database.

I am pretty sure that the command is not supported by Microsoft for the purpose it is being used for – so use with caution, at the direction of Microsoft Support, or not at all! It can cause undesirable side effects.

The reason that the customer is unable to play back logs is because this is not a database backup – while VSS or VDI IS a SQL backup, that allows the database to be attached in a recovery mode that would allow log playback to occur.

In order to get an opportunity to play back transaction logs, there are a few things you have to look at – the most important of which is the database’s RECOVERY MODEL. If a DBA truly wants point-in-time recovery via the application of T-LOG backups, then the recommended recovery model is FULL.

When a database is being restored, and a customer wants to “play back” logs (up to the tail log backup) the database must be restored with the “NO RECOVERY” switch set, as follows:

RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY

EMC leverages software (like Replication Manager, Appsync, KVSS, et al) that allows this option to be set at time of recovery of the snapshot. This is possible because it leverages VDI to achieve application consistency! All of the expected behavior of a SQL backup is performed - Checkpoint, etc.

A DBA could then proceed to restore logs up to the desired point in time (assuming that they are performing T-LOG backups – preferably to a nice Data Domain CIFS target) upon surfacing the snapshot!

Upon the last log backup restore that is applied, the customer would specify RECOVERY.

The moral of the story: You get one shot at recovery.

This is documented here:

http://msdn.microsoft.com/en-us/library/ms187495.aspx

I hope that this explains the SQL backup process sufficiently. I suspect that this particular solution will require RecoverPoint and Replication Manager in order to deliver the functionality that this DBA requires (i.e. point in time SQL recovery with the opportunity to replay T-log backups to a specific point in time from an application consistent snapshot, as well as the ability to take an application consistent snapshot for backup repurposing, as well as potentially limitless points in time for crash consistency, etc).

Under no circumstances would I recommend leveraging the deprecated DBCC FREEZE_IO command – there are too many unknowns for my comfort level.

For others that might be heading down the same road, the following resources are available that describe the EMC approach to SQL snapshots, backup and recovery:

·         RecoverPoint with SQL Server (SQL 2008R2, same for SQL 2012 but we just don’t need to replicate TempDB) http://www.emc.com/collateral/software/white-papers/h8221-sql-2008-data-protection-wp.pdf

o   EMC Replication Manager: Microsoft SQL 2008 R2 Crash Consistent Database Restore http://www.youtube.com/watch?v=YS7ewlSIkq8

o   Other videos on http://www.youtube.com/EMCProvenSolutions

·         RecoverPoint with VPLEX (Oracle paper but relevant) http://www.emc.com/collateral/hardware/white-papers/h10746-vplex-recoverpoint-oracle-dr-wp.pdf

·         Also Simple overview of the SQL Server Recovery Process https://community.emc.com/docs/DOC-13849