Recovery Models in SQL Server are basically designed to control the transaction log maintenance and to help you to recover your data from a disaster. The choice of a specific recovery model purely depends up on the criticality of the data which will be stored within the database. There are basically 3 different types of recovery models available in SQL Server 2000 and higher versions namely Simple, Full and Bulk Logged. The choice of a specific recovery model purely depends upon the criticality of the data which will be stored within the database.
These models each address different needs for performance, disk and tape space, and protection against data loss. For example, when you choose a recovery model, you must consider the tradeoffs between the following business requirements:
- Performance of large-scale operation (for example, index creation or bulk loads).
- Data loss exposure (for example, the loss of committed transactions).
- Transaction log space consumption.
- Simplicity of backup and recovery procedures.
Depending on what operations you are performing, more than one model may be appropriate. After you have chosen a recovery model or models, plan the required backup and recovery procedures.
This table provides an overview of the benefits and implications of the three recovery models.
|Recovery model||Benefits||Work loss exposure||Recover to point in time?|
|Simple||Permits high-performance bulk copy operations.Reclaims log space to keep space requirements small.||Changes since the most recent database or differential backup must be redone.||Can recover to the end of any backup. Then changes must be redone.|
|Full||No work is lost due to a lost or damaged data file.Can recover to an arbitrary point in time (for example, prior to application or user error).||Normally none.If the log is damaged, changes since the most recent log backup must be redone.||Can recover to any point in time.|
|Bulk-Logged||Permits high-performance bulk copy operations.Minimal log space is used by bulk operations.||If the log is damaged, or bulk operations occurred since the most recent log backup, changes since that last backup must be redone.Otherwise, no work is lost.||Can recover to the end of any backup. Then changes must be redone.|
For more information, see Selecting a Recovery Model.
Let us take a look at each of these recovery models in detail.
Simple Recovery Model
In Simple recovery model SQL Server will automatically truncate the transactional log file during the following scenarios.
- Whenever the transaction log file is 70% full
- A CHECKPOINT command is executed internally or it is executed manually
- Whenever the active portion of the transaction log file exceeds the size that SQL Server could recover within the time specified in recovery interval (min) parameter using
However, when a database is configured to use a Simple Recovery Model you will not be able to perform the transaction log backup this is by design from Microsoft.
Since you cannot take the transaction log backup, Point in Time recovery is not possible. You can restore your database only to the last available Full or Differential backups. Hence, this recovery model is best suited for user databases which are running in Development or Testing environments or a database which is configured as read-only.
Simple Recovery is not an appropriate choice for production systems where loss of recent changes is unacceptable.
For more information, see Simple Recovery.
Full Recovery Model
In Full Recovery model all the transactions are retained within the transaction log file until the log file is backed up. All the bulk operations such as
SELECT INTO, BULK INSERT, BCP, CREATE INDEX, ALTER INDEX, and
REBUILD INDEX etc are fully logged and it can be recovered using the available backups. When you are using Full Recovery Model, database administrator should use a combination of Full, Differential and Transactional Log backups as part of database backup plan to avoid any data loss.
By default whenever a new database is created it will be created using Full Recovery Model. This is because Model Database is configured to run under Full Recovery Model.
Database administrator should make sure that all the user databases in a Production environment are configured to use Full recovery model and you are using a combination of Full, Differential and Transactional Log to backups as part of database backup plan to avoid any data loss. This is because Point-in-Time recovery of the database only possible if you have all the valid database backups along with the transaction log tail backup.
Bulk Logged Recovery Model
When a database is configured to use a Bulk Logged Recovery Model then SQL Server will log minimal amount of information for operations such as SELECT INTO, BULK INSERT, BCP, CREATE INDEX etc within the transaction log file there by reducing the log file size. It is advisable to switch the recovery model from Full to Bulk Logged while performing Bulk Logged operations as this will help to reduce the log file growth and will thereby improve the database performance. However, as like in Full recovery model the transaction log will continue to grow until the log file is backed up
Click here to learn more about how the bulk-logged Recovery model works.