Different SQL Server Recovery Models

SQL query

You can also use the SQL query below to locate the recovery model in a database.

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'My_Database_Name' ;

Three types of recovery models in SQL Server

The backup and restore options for a database are determined by the three recovery models (simple, full, and bulk-logged). If there is a malfunction or crash in the database, we can restore the data based on the type of recovery model.

Simple recovery model

The simple recovery model is SQL Server’s default recovery model. With each completed transaction, this model automatically deletes the transaction log records. As a result, it only supports full or differential backups, not transaction log backups.

If the database fails or crashes, you can restore it using the most recent full or differential backup. When using the simple recovery model, we cannot perform a point-in-time restore. As a result, data that was modified between the time of the most recent full or differential backup and the time of failure may be lost.

Advantages

• The transaction logs are automatically removed by the simple recovery model, so the disk space required is minimal.
• In comparison to the full and bulk-logged recovery models, it requires the least amount of administration.

Disadvantages

• The simple recovery model has a significant disadvantage in that it does not support point-in-time restore. As a result, it may result in data loss during a database malfunction.
• This recovery model is not appropriate for an organization’s production databases where data loss will not be tolerated.

Full recovery model

SQL Server’s full recovery model keeps the transaction logs until they are backed up. Using this model, we can restore the database at any point in time without losing any data.

The full recovery model, like the simple recovery model, saves transaction logs. However, it will not automatically remove them after each completed transaction. Along with insert and update transactions, the transaction logs also record index creation and modification. Because each transaction is recorded, the log file grows enormously in size. As a result, administration must keep a close eye on the growing log size.

The database will stop accepting new transactions if the transaction log is full until it has been backed up or truncated.

Advantages

The data can be recovered to any specific point at will. So, this paradigm aids in preventing data loss.

Disadvantages

• The size of the transaction log file is enormous and it expands with each transaction.
• The administration must keep a tight eye on the expanding log size.
• The database will reject new transactions if the transaction log is full.

Bulk-logged recovery model

Similar to the full recovery concept is the bulk-logged recovery model. The main distinction is that when bulk-logged actions like bulk insert, select into, and build index are carried out, the transaction log size is lowered. In contrast to the complete recovery approach, the transaction log size in this model is modest.

Due to the low logging of the bulk transactions, the bulk-logged recovery approach also improves the performance of big bulk processes. This model may not always support the point-in-time restoration. Only if no bulk-logged activities were carried out at the time the database malfunctioned or crashed could a point-in-time restore be conducted.

We can only restore the database to the last transaction log before the bulk operation was recorded if you perform a bulk operation.

Advantages

• When compared to the full recovery model, the transaction log file size will not grow significantly.
• In the bulk-logged recovery model, point-in-time restore is also possible, but only in certain circumstances.
• Because of the minimal logging, this model improves the performance of bulk operations.

Disadvantages

In some cases, point-in-time restore is not supported.

How to change the recovery model

Before changing a database’s recovery model, we must examine the database’s activity. It is recommended that we change the recovery model when database activity is low.

We must create a full backup of the database before changing the recovery model. If something goes wrong, we can quickly restore it to its original condition.

You can change the recovery model of a SQL database in one of two ways.

SQL Server Management Studio

To change the recovery model in SQL Server Management Studio, follow these steps:

1. To begin, launch SQL Server Management Studio.
2. Right-click Database and choose Properties.
3. The Database Properties window will now open. From the left pane, select Options.
4. Then, from the drop-down menu, select the new recovery model.
5. And at last, click OK to save your changes.

SQL query

The following SQL query can also be used to modify a database’s recovery model.

USE [My_Database_Name] ;
ALTER DATABASE [My_Database_Name] SET RECOVERY FULL ;