SQL Server Recovery Models: A Quick Guide

 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

• Simple
• Full
• Bulk-logged recovery model

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

 

Simple

The 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.

Full

The 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-logged

Use 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?

There are multiple ways to determine which recovery model is being used. One option is to use SQL Server Management Studio tool to find the recovery model of a database. To do this, first right-click on a database, then select the “Properties” item from the drop-down. Once the database properties are displayed, select the “Options” item from the left context menu. When this is done, the window in Figure 1 will display.

Figure 1: Recovery Model option

Figure 1 shows the Recovery Model setting of Simple for the AdventureWorks2017 database.

Another way to display the recovery options for a database is to run the TSQL code found in Listing 1.

Listing 1: Code to Display Recovery Model

SELECT 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

In Listing 2, the database recovery model of the AdventureWorks2017 database was changed to FULL. Additionally, the Database Properties page of SSMS, as shown in Figure 1, can be used to change the database recovery model of a database. To change the recovery model, just select the correct option for the Recovery Model field and then click on the OK button.