SQL Server Recovery Models – What are they, types & which one to use?

SQL Server Recovery Model Overview

A SQL Server transaction log file (.ldf) is responsible for recording all of the database transactions and modifications made by each transaction. The LDF file information is useful for bringing the database back to a consistent state after a system failure. But the file can quickly grow in size, and if not maintained, it may lead to performance issues and corruption in the LDF file.

Quick Solution: Use Stellar Repair for MS SQL software to repair corrupt MDF database file and recover its transaction log (ldf) file in a few simple steps. The software supports the recovery of SQL database files on Windows as well as Linux systems.

Read this: How to Repair SQL Server Database with a Corrupt Log File?

However, you can control and manage the transaction log file size by using a SQL Server recovery model.

“A recovery model in SQL Server is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and determines the type of restore operations that are available.”

The recovery models essentially define the information that SQL Server writes to the transaction log file to perform database recovery. The types of database back-up and restore operations that you can perform are based on the type of recovery model selected.

Types of Recovery Models (SQL Server)

There are three recovery models in SQL Server: ‘Simple’, ‘Full’, and ‘Bulk_Logged’.

1. SQL Server Simple Recovery Model

As the name implies, the SIMPLE recovery model is the simplest compared to other available models. This recovery model supports three types of back-ups, including full, differential, and file level backups. It does not support backing up transaction logs.

Once the transaction gets completed, and data is written to the database file, the space can be reused by new transactions. Since the space is reused, you can easily manage the size of the transaction log.

With the Simple recovery model, you cannot perform point-in-time recovery of the SQL database. Instead, you may only restore it to a point when a complete (full) or differential back-up occurred.

When to Choose the Simple Database Recovery Model?

Use the simple recovery model when there is a necessity for:

• Restoring small, development, or test databases that do not contain critical data
• Point-in-time recovery of the database is not required
• Less database transaction

2.   SQL Server Full Recovery Model

This model helps restore the complete database. It allows recovery of the data to a specific point-in-time, provided that the back-up files are usable. Unlike the simple model, it does not automatically truncate the transaction log unless it is backed up.

You should consider using this model with log back-ups taken at periodic intervals when you have a production database. The transaction log will continue to grow if you won’t take the log back-up periodically.

When to Choose the Full Recovery Model?

Use the full recovery model when you need to:

• Restore database containing mission-critical data
• Perform point-in-time database recovery
• Perform recovery with zero or minimal data loss
• Recover individual pages

3.   SQL Server Bulk-logged Recovery Model

This model works similarly as the Full recovery model except that bulk operations (like bcp, BULK INSERT, SELECT INTO, etc.) are minimally logged (i.e., only the information needed to recover the transaction is logged). However, it cannot recover specific point-in-time data.

The full recovery model is the default recovery model used for normal operations. However, it is recommended that you switch the database to the bulk-logged recovery model temporarily when performing bulk operations. After completing the bulk operation, you can revert to the full recovery model.

When to Choose the Bulk-logged Recovery Model?

The bulk-logged model is better to use when:

• Minimal logging is required to prevent the growth of the log file
• Data is critical, and you want to reduce data loss without logging bulk operations
• High-performance bulk copy operations need to be performed

Changing SQL Server Database Recovery Models

Note: To change the recovery model, you will need permissions to run ALTER queries on the database.

You can change the recovery models as needed. For instance, when the database is set up in the Full recovery model, and you want to perform bulk operations that can be minimally logged, you can switch to the bulk-logged recovery model. Once the operations are complete, you can change your recovery model using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL).

Changing the Recovery Model using SSMS

The detailed steps are as follows:

Step 1: Launch SSMS.

Step 2: Browse your database name, and right-click on it.

Step 3: Select Properties.

Step 4: The Database Properties window is displayed. Click Options under ‘Select a page’ section from the left panel, and then select the Recovery model drop-down list.

Step 5: Save the settings by clicking OK.

Changing the Recovery Model using T-SQL

Choosing the Right SQL Server Recovery Model

Here’s a comparison table to help you decide which SQL Server recovery model you should choose:

Why You Should Choose a SQL Recovery Software? 

When restoring the database using the Full recovery model, the database remains offline during the entire restore process. This can result in increased downtime. Also, using the Simple or Bulk-logged model could expose the transaction log to data loss. Moreover, you may fail to restore severely corrupted SQL database files by using the recovery models.

A better alternative is to use a professional SQL recovery software that can help you quickly and easily restore the highly corrupt database without unnecessary downtime.

Stellar Repair for MS SQL is a SQL recovery tool that streamlines the process of performing database recovery, without executing complicated queries. It can also help restore a database marked in suspect mode or recovery mode. The advanced algorithm of the software can help parse a severely corrupt database and extract deleted records and tables.

Key Features of Stellar Recovery Tool:

• Allows recovery of all SQL database objects on both Windows and Linux systems with 100% integrity
• Repairs and recovers severely corrupt MDF and NDF database files
• Recovers deleted records, tables, keys, stored procedures, triggers, functions, views, etc.
• Previews recoverable SQL Server db objects
• Multiple file saving options, including MDF, CSV, XLS, and HTML formats

Conclusion

In this article, we have discussed the Simple, Full, and Bulk-logged SQL Server Recovery Models. Also, we have provided a comparison table to help you choose the right recovery model. Once you know which model to use, set up the appropriate recovery model and back-up plan to restore the database in the event of system failure. You can also change the default recovery model by using SSMS or T-SQL, as explained in this article. Keep in mind that running a full recovery model will require you to keep the db offline during the restore operation and increase downtime. And, using other available models involves data loss risk. You can minimize the downtime and avoid data loss risk when restoring the database by using Stellar Repair for MS SQL software.