SQL Server Database Recovery Phases
Database Recovery Phases
Different
phases of the recovery are
- Analysis
- Redo
- Undo
Analysis In this phase the transaction log is
analyzed and records the information about the last checkpoint and creates on
Dirty Page Table, this might capture all the dirty pages details.
Redo It’s a roll forward phase. When this phase
completes database becomes online so the database was online
Undo It’s rollback phase. It holds the list of
the active transaction from the analysis phase basically undoing the
transactions.
Database Startup Sequence
There
is an exact sequence that determines which user database will come online
first. The error log is the reference to measure the database startup sequence
but in any case
- Master database comes first as it stores system catalog details and configuration details of the user database
- Resource database comes next
- TempDb comes online only after model database comes online
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:
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.
Comments