SQL Recovery Model: Simple vs. Full

Introduction

There are three recovery models of SQL Server, and you may select the SQL Server recovery model to manage log files and make for the SQL recovery in case of disaster. This document is related to three SQL Server recovery models: simple, full, and bulk-logged. All of them are used to back up your SQL Server database.

SQL Server backup and reinstate operations happen within the framework of the recovery model of the database. SQL Server database comprises at least an MDF data file and a ldf log file. Mdf file holds all the database objects and data, such as the table, stored procedure, and user information. Recovery models are planned to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and permits) backing up, and what kinds of reinstating operations are available. These recovery models are simple, full, and bulk-logged. Usually, a database uses the full recovery model or simple recovery model. A database can be transferred to another recovery model at any time.

Summary

Simple

The Simple recovery model is the simplest among the existing models. It mechanically retrieves log space to keep space requirements small, basically eliminating the need to manage the transaction log space. For data about database backups under the simple recovery model. It is supported by full, differential, and file-level backups. Transaction log backups are not maintained. The log space is reclaimed whenever the SQL Server background process checkpoint operation happens. The inactive portion of the log file is detached and is made available for reuse.

Point-in-time and page restore are not reinforced, only the restoration of the secondary read-only file is maintained.

Some of the reasons to select the simple database recovery model include:

• It is most suitable for development and Test Databases
• Simple reporting or application database, where data loss is tolerable
• The point-of-failure recovery is exclusively for full and distinction backups
• No administrative overhead

It supports:

1. Full backup
2. Differential backup
3. Copy-Only backup
4. File backup
5. Partial backup

Advantage: It allows high-performance bulk copy operations, and regains log space to keep space requests small.

Disadvantage: It changes since the most recent database or discrepancy backup must be rebuilt.

Full

With the full recovery model, SQL Server reserves the transaction log until you back it up. In this recovery model, all the dealings (DDL (Data Definition Language) + DML (Data Manipulation Language)) are fully recorded in the transaction log file. The log order is unbroken and is preserved for the databases to restore operations. Unlike the Simple recovery model, the transaction log file is not auto-truncated during CHECKPOINT operations.

You get the most flexibility restoring databases using the full recovery model when a database failure occurs All reinstate operations are supported, including point-in-time restore, page restore, and file restore.

Reasons to select the full database recovery model:

• To support mission-critical applications
• Design High Availability keys
• To facilitate the recovery of all the data with zero or nominal data loss
• If the database designed to have multiple filegroups, and you want to perform a piecemeal restore of reading/write secondary filegroups and, optionally, read-only filegroups
• Allow random point-in-time restoration
• Restore individual sheets
• Sustain high administration overhead

It supports:

1. Full backup
2. Differential backup
3. Transaction log backup
4. Copy-Only backup
5. File and/or file-group backup
6. Partial backup

Advantage: No work is misplaced due to a lost or damaged data file. It can recuperate to a random point in time.

Disadvantage: If the log is damaged, changes since the most recent log backup must be rebuilt.

If the database is a development or a test server, the simple recovery model should mostly be adequate. However, if a database is a production one, it is normally recommended to go with a full recovery model. The full recovery model can be complemented by a bulk-logged recovery model. Of course, if your database is small, or is part of a data warehouse, or even if the database is read-only.

Bulk-Logged

This recovery model is ideal when you try to import massive data and do not want to increase the log with data.

If you had the full recovery model, importing massive data will increase the log file.

It is recommended to change the recovery mode during these scenarios:

• BCP operations
• When using INSERT with SELECT
• INSERT INTO clauses
• When using the WRITE, WRITETEXT, UPDATETEXT

When this option is set, the INDEX creation is minimally logged.

Conclusion

The choice of recovery model is mostly a business choice. The decision is based on what data the database holds, what amount of data loss is acceptable. If your group is already managing Full recovery mode databases, then keeping the Full recovery model selected is wise, However, if replies to the question "How do we manage disaster recovery for our SQL databases?" draws blank stares, giggles, or un-replied email, it is better to set up the database for the Simple recovery model, and make ensure that some regular "full backup" is completed, and the resultant backup file kept anywhere safe, the simple model may not be appropriate. At the same time, it is not feasible to use the full recovery model for every case, for obvious reasons of cost and complexity.