SQL Server Fast Recovery: What Is It and When Is It Needed?

Fast recovery is a feature in SQL Server Enterprise edition that allows accessing a database once the REDO (i.e., committed transactions are rolled forward) phase completes before the UNDO (i.e., uncommitted transactions are rolled back) phase gets complete. This article will talk in detail about the SQL Server Fast Recovery feature, when it is used, and when it can't be used. Also, the article discusses an alternative to performing a database restoration when Fast Recovery fails.

Before We Proceed

Let’s have a quick overview of the SQL Server recovery process that comprises of the following three phases:

1.  Analysis

As the name implies, this phase analyzes the transactions log. It determines the last checkpoint and creates a dirty page table (DPT). The table records information about the dirty pages at the time when SQL Server stopped. It also builds a table with information about active (uncommitted) transactions when the SQL Server stopped.

2.  Redo Phase 

This phase restores the database to the state when the SQL Server stopped and the database was shut down. During this time, transactions that are modified and recorded in a log file but may not have been written to the database are rolled forward.

3.  Undo Phase 

In this phase, the transactions that were left uncommitted in Phase 1 (Analysis) are rolled back.

A database becomes available after all these phases are completed. However, in Enterprise Edition, the database comes online right after the REDO phase.

When Is SQL Server Fast Recovery Used?

Introduced with SQL Server 2005, the Fast Recovery feature ensures that a database in an enterprise environment comes online after the REDO phase before the UNDO phase completes.

The Fast Recovery feature allows bringing an SQL database back online. It does so when an SQL Server instance is started and recovery process is run for the database in any of the following situations:

• A server crash or unclean system shutdown
• Failover of a High Availability and Disaster Recovery solution (like Database mirroring, cluster, or availability failover)
• When crash recovery needs to be performed on a database whose state has changed to ONLINE

When Is SQL Server Fast Recovery Not Used?

You cannot use the Fast Recovery feature of a SQL database when restoring a database from backups, or when attempting to bring a log shipped secondary database online by restoring it from backup.

Why You Cannot Use Fast Recovery for Database Restore

Fast Recovery does not help perform a restore operation because of its underlying mechanism. All of the operations that are run to make changes to a SQL database get logged. There’s a log record that contains details of the locks held on a database. During SQL Server crash recovery, all the locks needed to perform the UNDO phase are also acquired by the REDO phase. Essentially, the REDO phase knows the transactions that need to be rolled back in the log being recovered. So when the REDO phase is about to complete, a database is allowed to be accessible. This is because recovery guarantees that the UNDO phase cannot be blocked by any user, as the UNDO phase locks are already held.

Wondering why this mechanism does not work for RESTORE?

That’s because, unlike a crash recovery process, a database restore does not perform one REDO and one UNDO operation. A REDO phase is run for each database backup that is restored. Running a long REDO phase (which comprises tens of hundreds of committed transactions spread over multiple backups) after the restore procedure can be time-consuming. However, running the REDO phase while restoring a backup saves time. Once the restore procedure completes, all the REDO operations have been performed.

The REDO operations do not acquire UNDO locks, as UNDO is not likely to be the next phase during a database restore process. Chances are that some uncommitted transactions at the end of the last restore get committed before the next restore. Therefore, if any UNDO locks are acquired, they would need to be released, which requires rescanning the log records or tracking which in-restore transactions had acquired locks.

This is why fast recovery does not support database restores.

EndNote

SQL Server Fast Recovery is a useful feature to perform crash recovery of databases in an enterprise environment.