Recovery Models in SQL Server are basically designed to control the transaction log maintenance and to help you recover your data from a disaster. There are basically three 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 up on the criticality of the data which will be stored within the database.
A database can be switched to another recovery model at any time.
Let us now take a look at each of these recovery models in detail.
Basic information about log files
SQL Server database contains at least a mdf data file and a ldf log file. Mdf file contains all the database objects and data, such as table, stored procedure and user information. Ldf log file contains all logs in database. While, you never only count on log files to recover your database, for example, to recover database to some time-point through ldf log file and the previous full backup.
What does log file do?
- Being physical store position of logs, we can backup the logs from ldf log file, and recover database using log backup file.
- SQL Server will read the log from ldf log file every time it launches. It will roll backup affairs not submitted before and roll forward those affairs which have been submitted but not have been wrote data file to ensure the integrity of affairs.
- A full backup contains all data of database, but not including all logs. Full backup only contains all data pages and logs in the tail of current database.
Activity log means log records of current running affairs. Virtual log means logic storage unit of log. SQL Server logically divides ldf log file space into several pieces. Every piece is a virtual log. Log truncation means to delete former log.
Find More Information In: transaction-log-overview
1-Simple Recovery Model
When you choose simple recovery model, SQL Server maintains only a minimal amount of information in the transaction log. SQL Server truncates the transaction log each time the database reaches a transaction checkpoint, leaving no log entries for disaster recovery purposes.
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 inrecovery interval (min) parameter using SP_CONFIGURE.
In databases using the simple recovery model, you may restore full or differential backups only. It is not possible to restore such a database to a given point in time, you may only restore it to the exact time when a full or differential backup occurred. Therefore, you will automatically lose any data modifications made between the time of the most recent full/differential backup and the time of the failure.
Simple Recovery requires the least administration. It is easier to manage than the Full or Bulk-Logged models, but at the expense of higher data loss exposure if a data file is damaged. Simple Recovery is not an appropriate choice for production systems where loss of recent changes is unacceptable. When using Simple Recovery, the backup interval should be long enough to keep the backup overhead from affecting production work, yet short enough to prevent the loss of significant amounts of data.
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.
Advantage: permits high-performance bulk copy operations. Reclaims log space to keep space requirements small.
Disadvantage: changes since the most recent database or differential backup must be redone.
Important Note: – A very common misunderstanding is that when a database is configured to use Simple recovery model nothing is logged. However this is not at all true. In Simple recovery model everything is logged but Point in Time is not possible as it’s not possible to take the transaction log backup. At the same time the Bulk operations are logged minimally as like in Bulk Logged recovery model.
2-Full Recovery Model
With full recovery model, SQL Server preserves the transaction log until you back it up. This allows you to design a disaster recovery plan that includes a combination of full and differential database backups in conjunction with transaction log backups.
In Full recovery model 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.
You have the most flexibility restoring databases using the full recovery model when a database failure happens. In addition to preserving data modifications stored in the transaction log, the full recovery model allows you to restore a database to a specific point in time.
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. 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.
Advantage: no work is lost due to a lost or damaged data file. It can recover to an arbitrary point in time.
Disadvantage: if the log is damaged, changes since the most recent log backup must be redone.
3-Bulk-logged Recovery Model
The bulk-logged recovery model is a special-purpose model that works in a similar manner to the full recovery model. The only difference is in the way it handles bulk data modification operations. The bulk-logged model records these operations in the transaction log using a technical known as minimal logging. This saves significantly on processing time, but prevents you from using the point-in-time restore option.
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.
Advantage: permits high-performance bulk copy operations, minimal log space is used by bulk operations.
Disadvantage: if the log is damaged, or bulk operations occurred since the most recent log backup, changes since that last backup must be redone.
Switching between full and bulk-logged recovery models is useful before and after large bulk operations. The full recovery model, which fully logs all transactions, is intended for normal use. The bulk-logged recovery model is intended to be used temporarily during a large bulk operation
We recommend that you use the full recovery model the rest of the time. As soon as a set of bulk operations finishes, we recommend that you immediately switch back to the full recovery model.
we recommend that you switch to the bulk-logged recovery model only under the following conditions:
- Users are currently not allowed in the database.
- All modifications made during bulk processing are recoverable without depending on taking a log backup; for example, by re-running the bulk processes.
We recommend that:
- Before switching to the bulk-logged recovery model, you back up the log.This is important because, under the bulk-logged recovery model, if the database fails, backing up the log for bulk operations requires access to the data.
- After performing the bulk operations, you immediately switch back to full recovery mode.
- After switching back from the bulk-logged recovery model to the full recovery model, you back up the log again.
Following these recommendations fully protects your data and enables point-in-time recovery. The following figure illustrates these recommendations.
When switching between two recovery models, your backup strategy remains the same: continue performing periodic database, log, and differential backups.
Full Recovery and Bulk-Logged Recovery models provide the greatest protection for data. These models rely on the transaction log to provide full recoverability and to prevent work loss in the broadest range of failure scenarios. The Bulk-Logged model provides higher performance and lower log space consumption for certain large-scale operations.
When you choose different MS SQL Server backup strategies, you will have specific limitations to recover SQL Server backups.
The following table summarizes the three recovery models.
|Recovery model||Description||Work loss exposure||Recover to point in time?|
|Simple||No log backups.|
Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space. For information about database backups under the simple recovery model, see Full Database Backups (SQL Server).
Operations that require transaction log backups are not supported by the simple recovery model. The following features cannot be used in simple recovery mode:
-Always On or Database mirroring
-Media recovery without data loss
|Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.||Can recover only to the end of a backup. For more information, see Complete Database Restores (Simple Recovery Model).|
|Full||Requires log backups.|
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). For information about database backups under the full recovery model, see Full Database Backups (SQL Server)and Complete Database Restores (Full Recovery Model).
If the tail of the log is damaged, changes since the most recent log backup must be redone.
|Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For information about using log backups to restore to the point of failure, see Restore a SQL Server Database to a Point in Time (Full Recovery Model).|
Note: If you have two or more full-recovery-model databases that must be logically consistent, you may have to implement special procedures to make sure the recoverability of these databases. For more information, see Recovery of Related Databases That Contain Marked Transaction.
|Bulk logged||Requires log backups.|
An adjunct of the full recovery model that permits high-performance bulk copy operations.
Reduces log space usage by using minimal logging for most bulk operations. For information about operations that can be minimally logged, see The Transaction Log (SQL Server).
|If the log is damaged or bulk-logged 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. Point-in-time recovery is not supported.|
Set Types Of Recovery model using Management Studio
- Right click on database name and select Properties
- Go to the Options page
- Under Recovery model select a Recovery Model.
- Click “OK” to save
OR Using Command:
ALTER DATABASE AdventureWorks SET RECOVERY FULL
ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLEGO