A recovery model in a Structured query language (SQL) Server manages the transaction logs and handles how they are logged, backed up, and restored.For detailed information on backing up SQL Server through SQL Server Management Studio, see http://msdn.microsoft.com/en-us/library/ms187510.aspx.For a complete overview of SQL Server recovery models, see https://msdn.microsoft.com/en-us/library/ms175987(v=sql.105).aspx
In this blog post, we will see how to find the existing model in our database, the pros, and cons of the three recovery models in SQL Server, and how to change the model.The SQL Server database administrator can assign each database a different recovery model, but specific recovery models are assigned to each database type by default.
SQL Server system database type | Default recovery model |
master | Simple |
tempdb | Simple |
model | Full |
msdb | Simple |
distribution | Full |
SimpleThe simple recovery model is the most basic one and requires the least amount of administration
of the three models.
- It only supports full and differential database backups, and log backups are not possible.
- If a failure occurs, you lose all changes made since the last full or differential backup.
- You should not use this recovery model for production databases.
- The system writes new data points to the transaction log file. However, after writing the data to the file, the
system can reuse that space. Therefore, the transaction log file does not grow indefinitely and never becomes full.
Advantages- The simple recovery model will automatically remove the transaction logs, so the disk size required is very small.
- It requires minimum administration comparing to the full and bulk-logged recovery models.
Disadvantages- The major drawback of the simple recovery model is that it does not support point-in-time restore. So, it may cause a loss of data during a database malfunction.
- This recovery model is not suitable for production databases of an organization where data loss will not be accepted.
FullThe full recovery model enables you to restore to a specific point in time without data loss. To ensure a
successful recovery without any data loss, use transaction log backups.
- The transaction log file stores all transaction data.
- The log file grows until a log backup completes, or the log file truncates. Therefore, you need to set up
transaction log backups to keep the log file from growing indefinitely.
Advantages- We can restore the data to any arbitrary point. Thus, this model helps us avoid data loss.
Disadvantages- The transaction log file size is huge, and it grows in size with every transaction.
- It requires the administration to closely monitor the growing log size.
- If the transaction log is full, then the database will reject further transactions.
Bulk-loggedUse the bulk-logged recovery model to perform large bulk-copy operations. Similar to the full recovery model,
bulk-log uses log backups.
- This model uses minimal logging when writing transactions to the transaction log file. While this saves time
and disk space, minimal logging can prevent point-in-time restores. - You can still recover to a specific time, as long as the most recent transaction log does not include bulk
operations. - You need to set up transaction log backups to keep the log file from growing indefinitely. If the transaction
log gets damaged, you lose all changes since the last full or differential backup.
Advantages- The transaction log file size will not grow massive in size when comparing to the full recovery model.
- The point-in-time restore is also possible in the bulk-logged recovery model but only in certain cases.
- This model increases the performance of bulk operations due to minimal logging.
Disadvantages- Point-in-time restore is not supported for certain cases.
Which Recovery Model is being Used?
Listing 1: Code to Display Recovery ModelSELECT name, recovery_model_desc FROM sys.databases WHERE name = 'AdventureWorks2017' ;
Changing the Recovery Model
Over time the recovery model for a database might need to change. This might occur when an application requires more or fewer recovery options for a given database. If the recovery model needs to be changed, it can easily be changed by running the code in Listing 2.
USE master;
GO
ALTER DATABASE AdventureWorks2017 SET RECOVERY FULL;
GO