Transaction Log Recovery Models in SQL Server

Context

The SQL Server database holds at least one MDF data file and one LDF log file. The MDF file holds all of the database's objects and data, such as the table, stored procedures, and user information.

The LDF log file holds all of the database's logs. Never rely on log files alone to recover a database; for example, to recover the database at a certain point in time, both the LDF log file the and previous full backup are needed.

What does the log file do?

Being the physical location of logs, it is possible to save logs through the LDF log file and recover the database with the help of the log backup file.
SQL Server reads the log file through the LDF file at every launch. It will first move forward the backup operations that have not been submitted and will then move the operations that have been submitted, but that were not written to ensure their integrity.
A full backup holds all the data of a database, but not all the logs. The full backup only contains all of the data pages and journals found at the end of the current database.

Summary

Recovery Model Types
Choosing a Recovery Model
Displaying and Modifying the Recovery Model

Recovery Model Types

Simple Recovery Model

When you choose the simple recovery model, SQL Server only stores a minimal quantity of information in the transaction log. SQL Server truncates the transaction log every time the database reaches a transaction checkpoint, leaving no log entries, for disaster recovery purposes.

You can only restore, in databases that use a simple recovery model, full or differential backups. It is impossible to restore a database for a given moment, you can only restore it at the exact time a full or differential backup occured. As a result, you will automatically lose all data changes made between the time of the most recent full / differential backup and the time of failure.

Advantage: allows high performance bulk copy operations. Recovers log space to limit the need of space requirements.
Disadvantage: modifications made since the last full or differential backup must be redone.

Full Recovery Model

When using the full recovery model, SQL Server keeps the transaction log until the next backup. This allows you to design a disaster recovery plan that includes a combination of full and differential database backups. in conjunction with transaction log backups.

You have at your disposal the greatest flexibility to restore databases using the full recovery model in the event of a database failure. On top of saving the modification of data stored in the transaction logs, the full recovery model allows you to restore a database at a precise moment.

Advantage: no transaction will be lost due to a damaged data file. It can recover data from any specific time.
Disadvantage: if the log is damaged, the modifications made since the log's last backup will have to be redone.

Bulk-Logged Recovery Model

The bulk-logged recovery model is a special purpose model that works in a similar way to the full recovery model. The only difference resides in the way it manages bulk data modification operations. The bulk-logged recovery model saves its operations in the transaction log using a technique called minimal logging. This considerably reduces the process time, but stops you from using the specific time restoration option.

Advantage: allows high performance bulk copy operations, minimal log space is used by bulk operations.
Disadvantage: if the log is damaged or if the bulk operations have been made since the last log backup, the modifications made since this last backup will have to be redone.

Choosing a Recovery Model

The Full Recovery and Bulk-logged recovery models offer the best data protection. These models rely on the transaction logs to perform a complete recovery and avoid the loss of data in a large number of different failure scenarios. When you choose an SQL Server backup strategy, you have specific limitations to recover SQL Server backups.

Displaying and Modifying the Recovery Model

Connect to the appropriate SQL Server database engin instance. In the Object Browser, click on the server name to open it's tree structure.
Develop Database then, according to the database, select a user or software database.
Make a right-click on the database, then click on Properties to open the Database Properties dialogue box.
In the Select a page section, select a page and click on Options.
The current recovery model is displayed in the Recovery Model list zone.
To modify the recovery mode, select another model in the list. The choices are Full, Bulk-logged, or Simple.