Database Recovery Models.
The feature “database recovery model” was included by Microsoft since SQL Server 2000. It helps us to manage the transaction log. Database Recovery model is part of the recovery options that SQL Server offers. The other option besides of recovery model is PAGE_VERIFY (CHECKSUM or TORN_PAGE_DETECTION), this option detect corrupt pages of the database because I/O problems. PAGE_VERIFY is not explain in this article.
SQL Server 2000, 2005 and 2008 offers three recovery models:
- Simple Recovery Model
- Bulk-logged Recovery Model
- Full Recovery Model
The recovery model is defined by each database and it can be changed according to business necessities. The following information show us details of each recovery model.
Simple Recovery Model.
- Help to minimize the overhead over the transaction log, because transaction log is not backed up.
- Reduces the space used for transaction log on disk, because the transactions are not remaining on the transaction log, they are truncated.
- This recovery model logs minimal amount of information on the transaction log, logging only the information necessary to ensure the database consistency after a restore from a backup or after system crash.
- SQL Server truncates the log when a checkpoint occurs. Checkpoint truncates the log removing committed transactions, it is performed automatically by SQL Server at regular intervals. The intervals can be configured with Recovery Interval option using sp_configure. Is important to know that exists factors that can delay the log truncation.
- No log backup is supported. It means that if a disaster happens, a restore to point in time can not be performed and the database must be restore until the last full backup or differential backup. Therefore, the databases under this recovery model must be backed up frequently to avoid loss of data as much as possible.
- Simple recovery model is advisable to use for user databases, development and testing scenarios. Is not recommendable to use in Production systems. For production systems is recommendable Full Recovery Model.
Bulk-logged Recovery Model.
- Bulk-logged recovery model provide better protection of data than simple recovery model.
- Bulk-logged recovery model works in similar way than full recovery model. The only difference is the way to handle bulk operations. If there are not bulk operations running, bulk-logged recovery model works similar than full recovery model.
- This recovery model offers minimal logging for Bulk operations as BCP, SELECT INTO, BULK INSERT, CREATE INDEX, reducing the log space consumption.
- Microsoft recommends use bulk-logged recovery model for short periods of time. A best practice is switch the recovery model from full recovery model to bulk-logged recovery model before perform bulk operations and restore it to full recovery model when those operations are completed.
- This model is recommended for user database where bulk operations are performed when no users using the database.
- For log backup that contains bulk operations, the database can be recovered just to end of the log backup and the point in time (STOPAT) recovery is not allowed. If the log backup does not have bulk log operations logged, then the point in time recovery is allowed.
Full Recovery Model.
- This model is recommended for production databases, critical environments and in environment where you cannot afford to lose data.
- Support the recovery to point in time, assuming the log backups are complete up to the point in time desired.
- This recovery model offers fully logs for all the transaction on the database. Even logs any bulk operation.
- Transaction log, logs all the transactions of the database and the transactions are retain until a log backup is performed. Log backup is supported.
How to check the recovery model
To know the current recovery model the DBA can check it using TSQL or Graphical tool (SSMS).
Using TSQL:
| SELECT DATABASEPROPERTYEX('SID', 'RECOVERY') |
Or
| select recovery_model, recovery_model_desc from sys.databases where database_name = 'SID' |
How to switch the recovery model
The recovery model can be modified on TSQL and with Graphical tool (SSMS).
Using TSQL:
| --Switch to simple ALTER DATABASE <SID> SET RECOVERY SIMPLE GO --Switch to Bulk logged ALTER DATABASE <SID> SET RECOVERY BULK_LOGGED GO --Switch to full ALTER DATABASE <SID> SET RECOVERY FULL GO |
To switch the recovery model is necessary to have permissions to execute ALTER statement.
Important Considerations when switching recovery model
The recovery model should be defined depending the business necessities. But, is important to know the impact that has switching between one recover model to another.
Simple -> Full / Bulk logged
This switch is recommendable when is a productive database. For simple recovery model, the transaction log is truncated in every checkpoint, but, in full/bulk logged the transaction log is not truncate until a log backup is performed. When switch to bulk logged every transaction is fully logged except bulk transaction as SELECT INTO, but in full recovery model all the transaction are fully logged.
Bulk logged -> Full
When bulk task are finished is necessary switch to full recovery model then is recommendable take a log backup.
Full -> Bulk Logged
This switch is recommendable when a bulk task is going to be performed in order to minimize the log space used. A log backup should be performed before switch to Bulk logged and is recommendable no users connected when bulk logged recovery model is defined.
Full/Bulk logged -> Simple
This switch is not recommendable for productive databases. The log will be truncated in every checkpoint and the log backup is not supported. A log backup should be performed before switch to simple. You switch to simple before a full backup to decrease the file size of the backup.
Conclusion
The person in charge of the database should be necessary to understand the recovery models in SQL Server also the impact that has to use each of them; this article shows important information to understand that.
