Instance Recovery in SQL Server?

Question

When the instance crashes due to unexpected disaster in SQL Server, how does the SQL Server recover the databases?

SQL Server goes through which phases to recover the data and which are the steps that are performed in the instance recovery by SQL Server?

Answer 1

(Copied from https://stackoverflow.com/questions/2391399/three-phases-of-recovery-the-analysis-phase-the-redo-phase-and-finally-the-u)

You might find this document interesting : Simple overview of the SQL Server Recovery Process

Phases of recovery

the recovery algorithm has 3 phases based around the last checkpoint in the transaction log.

Phase 1: Analysis. Starts at the last checkpoint in transaction log. This pass determines and constructs a dirty page table (DPT) consisting of pages that might be dirty at the time SQL Server stopped. An active transaction table is built of the uncommitted transactions at the time of the SQL Server stopped also.

Phase 2: Redo. This phase returns the database to the state at the time the SQL service stopped. Starting point for this forward passbeing the oldest uncommitted transaction. The mininum Log Sequence name (each log record is labelled with an LSN) in the DPT is the first time SQL Server expects to have to redo an operation on a page, redoing the logged operations starting right back at the oldest open transaction so that the neccessary locks can be aquired.

Phase 3: Undo: Here the list of active transaction (uncommitted at the time SQL Server stoopped) which where indentified in Phase 1 are rolled back individually. SQL Server follows the links between entries in the transaction log for each transaction. Any transaction that was not committed at the time SQL Server stopped is undone.

Recovery can be done when you restore the database, but it is also done at the startup of the database (crash recovery).

Answer 2

Instance Recovery Phases

The possible phases of a restore include the data copy, redo (roll forward), and undo (roll back) phases:

Data Copy Phase

• The first phase in any restore process is the data copy phase. The data copy phase initializes the contents of the database, files, or pages being restored.
• This phase is performed by restore database, restore file, and restore page operations using full or differential backups.
• The data copy phase involves copying data from one or more full backups and, optionally, differential backups, and then resetting the contents of the affected database, files, or pages to the time that they were captured by those backups.
• The oldest file or page in the roll forward set determines the starting point for the next phase: redo (roll forward).

Redo Phase (Roll Forward)

• Redo (or roll forward) is the process of redoing logged changes to the data in the roll forward set to bring the data forward in time.

• To accomplish roll forward, the SQL Server Database Engine processes log backups as they are restored, starting with the log that is contained in full backups, Restore avoids unnecessary roll forward.

• Generally, if data was read-only when it was backed up and has remained read-only, roll forward is unnecessary and is skipped.

The Recovery Point

The goal of roll forward is to return the data to its original state at the recovery point. The recovery point is the point to which the user specifies that the set of data be recovered. Under the full recovery model, you can specify the recovery point as a particular point in time, a marked transaction, or a log sequence number. Under the bulk-logged recovery model, you can restore to a point in time only if no bulk operations have been performed since the previous log backup.

Redo Consistency In the redo phase, data is always rolled forward to a point that is redo consistent with the state of the database at the recovery point. All the data has been rolled forward to a point at which undo can occur.

The state of the database is defined by the primary file, as follows:

• If the primary file is being restored, the recovery point determines the state of the whole database. For example, if a database is being recovered to a point in time just before a table was accidentally dropped, the whole database must be restored to the same point in time.
• If the primary file is not being restored, the database state is known and restored data is rolled forward to a recovery point that is transactionally consistent with the database. SQL Server enforces this.

However, the database might contain changes made by transactions that are uncommitted at the recovery point. For online restore, data is recovered to a point in time consistent with the current state of the online part of the database.

A differential backup skips forward to when the differential backup was taken. Pages in the roll forward set are overwritten with any more recent ones from the differential backup.

Undo (Roll Back) Phase and Recovery

• After the redo phase has rolled forward all the log transactions, a database typically contains changes made by transactions that are uncommitted at the recovery point. This makes the rolled forward data transactionally inconsistent.
• The recovery process opens the transaction log to identify uncommitted transactions. Uncommitted transactions are undone by being rolled back, unless they hold locks that prevent other transactions from viewing transactionally inconsistent data. This step, is called the undo (or roll back) phase.
• If the data is already transactionally consistent at the start of the recovery process, the undo phase is skipped. After the database is transactionally consistent, recovery brings the database online.

After one or more backups have been restored, recovery typically includes both the redo and undo phases. Every full and differential backup contains enough transaction log records to allow for the data in that backup to be recovered to a self-consistent state.

For more information you check the below mentioned link:

https://technet.microsoft.com/en-us/library/ms191455(v=sql.105).aspx

In Addition to the above answer ; I also would like add some more detailed information which I came across while doing some research on it:

Steps that are performed by SQL Server while performing instance Recovery are as follows:

Instance recovery aims at:

• writing all committed changes to the datafiles
• undoing all the uncommitted changes from the datafiles
• Incrementing the checkpoint no. to the LSN till which changes have been written to datafiles.

Before the instance crashes:

• Some committed changes are in the Log files but have not been written to the datafiles
• Some uncommitted changes have made their way to datafiles
• Some uncommitted changes are in the Log buffer / Cache

After the instance crashes:

• All uncommitted changes in the Log buffer/Cache are wiped out
• Log files are read to identify the Pages that need to be recovered
• Identified Pages are read from the datafiles
• During roll forward phase, all the changes (committed/uncommitted) in redo log files are applied to them
• During rollback phase, all uncommitted changes are rolled back
• Checkpoint's LSN is updated in data file/Log Files headers.