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. The SQL Server database contains a master database file (MDF) and a log database file (LDF). The MDF data file consists of all the database objects, such as tables, stored procedures, and information. The LDF log file consists of all the logs, such as transactions that occur in a database.  There are three types of recovery models in SQL Server:

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

How to find the existing recovery model in SQL Server

You can check what the existing recovery model in your database is using either of the following ways.

SQL Server Management Studio

Follow these simple steps to find out what the existing recovery model is using the SQL Server Management Studio:

1. First, open the SQL Server Management Studio.
2. Then, right-click on the Database and select the Properties option.
3. Now, the Database Properties window will appear. In that, select Options from the left pane. Then, it will display the current recovery model in your database.
   Refer to the following screenshot.
   

SQL query

You can also use the following SQL query to find 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 three recovery models (simple, full, and bulk-logged) determine the backup and restore options for a database. Based on the type of the recovery model, we can restore the data if there is a malfunction or crash in the database.

Simple recovery model

The basic recovery model in SQL Server is the simple recovery model. This model automatically removes the transaction log records on every completed transaction. So, it doesn’t support transaction log backups, only full or differential backup.

If there is a malfunction or crash in the database, then you can restore it with the recent full or differential backup. We cannot perform a point-in-time restore when using the simple recovery model. So, this may lead to a loss of the data that was modified between the time of recent full or differential backup and the time of failure.

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 recovery model

The full recovery model in SQL Server maintains the transaction logs until it is backed up. Using this model, we can restore the database at any point in time and there will not be any data loss.

Like the simple recovery model, the full recovery model records the transaction logs. But, it will not remove them automatically on each completed transaction. Along with the insert and update transactions, the transaction logs also record the creating and altering indexes. This process makes the log file size huge, as each transaction is recorded. So, this process requires administration to closely monitor the growing log size.

If the transaction log becomes full, the database will not accept further transactions until the log file is either backed up or truncated.

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 recovery model

The bulk-logged recovery model is similar to the full recovery model. The only difference is that the transaction log size is minimized when performing bulk-logged operations like bulk insert, select into and create index. So, the transaction log size in this model is small when comparing to the full recovery model.

Additionally, the bulk-logged recovery model increases the performance of large bulk operations, due to the minimal logging of the bulk transactions. In some cases, this model won’t support the point-in-time restore. The point-in-time restore can be done only if no bulk-logged operations are performed during the time of database malfunctioning or crashes.

If you perform a bulk operation, then we can restore the database only to the last transaction log before the bulk operation was recorded.

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.

How to change the recovery model

Before changing the recovery model of a database, we need to check the database activity. It is recommended that we change the recovery model during a time of lower database activity.

Before changing the recovery model, we need to make a full backup of the database. If anything goes wrong, we can immediately restore it to its original state.

You can change the recovery model of an SQL database using either of the following ways.

SQL Server Management Studio

Follow these steps to change the recovery model using the SQL Server Management Studio:

1. First, open the SQL Server Management Studio.
2. Then, right-click on Database and select Properties.
3. Now, the Database Properties window will appear. In that, select Options from the left pane.
4. Then, select the new recovery model from the drop-down.
5. Finally, click Ok to reflect the changes.
   

SQL query

You can also use the following SQL query to change the recovery model of a database.

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

Conclusion

Thanks for reading! I hope you have a clear idea of the three different recovery models in SQL Server, how to check the existing one, and how to change it in your database. Try out the steps in this blog post and leave your feedback in the comments section!

Syncfusion has over 1,600 components and frameworks for WinForms, WPF, WinUI, ASP.NET (Web Forms, MVC, Core), UWP, Xamarin, Flutter, JavaScript, Angular, Blazor, Vue, and React. Use them to boost your application development speed.

For existing customers, the new version is available for download from the License and Downloads page. If you are not yet a Syncfusion customer, you can try our 30-day free trial to check out our available features.

You can also contact us through our support forum, Direct-Trac, or feedback portal. We are always happy to assist you!

If you like this blog post, we think you’ll also like the following useful articles:

• Top 10 SQL Query Optimization Techniques [Blog]
• How to Export Data from SQL Server to Excel Table in C# [Blog]
• How to Migrate SQL Server in an ASP.NET MVC Application to MySQL and PostgreSQL [Blog]
• SQL Server Metadata Succinctly [Ebook]
• SQL Queries Succinctly [Ebook]
• SQL on Azure Succinctly [Ebook]