SQL Server recovery mode

The recovery model is designed to control transaction log maintenance. The recovery mode illustrates the risk of job loss. Can it be recovered at that time?

SQL Server databases have three recovery modes: simple recovery mode, full recovery mode, and large-capacity log recovery mode.

Compared with the simple recovery mode, the full recovery mode and the large-capacity log recovery mode provide stronger data protection functions. These recovery modes are based on the backup transaction logs to provide complete recoverability and prevent loss of work within the maximum range of failures. Generally, the database adopts the full or simple recovery mode.

 

The following is a comparison of the three recovery modes:

Recovery mode	Log backup	Recovery Point	Advantages	Disadvantages	Solutions and suggestions
Simple)	No log backup. The log space is automatically reclaimed to reduce space requirements. In fact, you no longer need to manage the transaction log space.	Changes made after the latest backup are not protected. In the event of a disaster, these changes must be redone. It can only be restored to the end of the backup.	The simple recovery mode minimizes the management overhead of transaction logs because transaction logs are not backed up.	If the database is damaged, the simple recovery mode faces a great risk of job loss. Data can only be restored to the latest backup of lost data.	In simple recovery mode, the backup interval should be as short as possible to prevent massive data loss. The simple recovery mode is not suitable for the production system because it is unacceptable for the production system to lose the latest changes. In this case, we recommend that you use the full recovery mode.
Full)	Log backup is required.	Theoretically, it can be recovered to any point in time.	Data File loss or damage will not cause loss of work.	This mode completely records all transactions, occupying a large amount of space.
Bulk-logged)	Log backup is required.	If logs are corrupted after the latest log backup or large-capacity logging is performed, you must redo the changes made since the last backup. It can be restored to the end of any backup. Point-in-time recovery is not supported.	This mode is an additional mode for the full recovery mode, allowing high-performance large-capacity replication operations. You can use the minimum method to record most large-capacity operations to reduce the log space usage.	Saves log storage space than full mode.	For some large-scale large-capacity operations (such as large-capacity import or index creation), switching to the large-capacity log recovery mode temporarily improves performance and reduces log space usage. Because the large-capacity log recovery mode does not support point-in-time recovery, you must weigh between increasing log backup and increasing the risk of job loss.

Note:
1. The recovery mode suitable for databases depends on Database Availability and recovery requirements.

2. Log backup is required in both full recovery mode and large-capacity log recovery mode. If you do not want to back up logs, use the simple recovery mode.