By Wang Jianming, Senior Engineer
This article describes the relationship between the three SQL Server database recovery models and backups. The following are the three database recovery models in SQL Server:
In the Simple recovery model, database transaction logs are cleared along with the Checkpoint or Backup operation to minimize transaction logs.
I think the name "Simple" does not accurately indicate how a database works under this model. A more accurate name is "Checkpoint with truncate log." In detail, all committed transactions are cleared upon completion of the Checkpoint or Backup operation, with only a few logs kept, necessary for recovery when an instance restarts. This model can minimize database transaction logs and storage usage, reduce storage overhead, and eliminate the need for special DBAs to maintain and back up database logs.
However, this model has obvious disadvantages. For example:
According to the aforementioned principles of the Simple database recovery model, we can easily find applicable scenarios for the Simple model, including:
The Full model in SQL Server is quite the opposite of the Simple recovery model. This section shows the following four aspects about the Full model: working principles, application scenarios, setting, and example scenarios.
In contrast to Simple, we can consider the Full model as "Checkpoint without truncate log," that is, the SQL Server database engine does not truncate transaction logs. Therefore, compared with databases using the Simple model, databases using the Full model have transaction log files that increase faster and are much larger. These database log files contain all recently committed transactions until a transaction log backup occurs and finishes successfully.
Therefore, databases using the Full model have the following features:
Now that we have described the Full model, let us take a look at applicable scenarios for the Full model, including:
Of course, compared with the Simple model, the transaction log files in the Full model have a higher growth speed and range. Therefore, DBAs need to maintain, monitor, and back up database transaction logs.
As a mix of the Simple and Full recovery models, the Bulk-logged model adapts and improves the Bulk Imports operation under the Full model.
In a SQL Server database system, a method called Bulk Imports is available for quickly importing data, such as BCP, Bulk INSERT, and INSERT INTO... SELECT. If these Bulk operations are performed in a database under the Full model, massive amounts of log information are generated, significantly influencing SQL Server performance. The Bulk-logged model is designed to solve this problem. When a Bulk Imports operation is performed in a database running under the Bulk-logged model, very few logs are recorded to prevent the sharp increase in transaction logs and guarantee stable and efficient SQL Server performance. Simply, when no Bulk Imports operations are performed, the Bulk-logged model is equivalent to the Full model; when a Bulk Imports operation is performed, it is equivalent to the Simple model. Therefore, databases using the Bulk-logged model cannot implement point-in-time recovery. This is also a disadvantage in the Simple model.
Based on Bulk-logged model principles, applicable scenarios include:
Now that we know some applicable scenarios for the Simple model, let us look at how to set database recovery to the Simple model. To do this, we can use either of the following two methods.
Method 1: SSMS IDE
Right-click on the name of the target database to change the recovery model, and then choose Properties > Options > Simple (in the "Recovery model" box on the right) > OK.
Method 2: A statement
If you think that using SSMS IDE is too complex, you can use the ALTER DATABASE statement to change the recovery model of a database to Simple. The following statement changes the recovery model of the AdventureWorks2008R2 database to Simple.
USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY SIMPLE WITH NO_WAIT
GO
The following figure shows an example scenario of the Simple model:
Note: The preceding figure is from https://sqlbak.com/academy/simple-recovery-model/
In this example, the following events took place
At this point, we can at most retrieve the data in the differential backup file created at 16:00. Data stored between 16:00 and 22:00 is lost and cannot be retrieved. That means that we can at most retrieve data in the last backup file before the exception point in time. To retrieve the data, we must recover the full backup created at 10:00 first and then the differential backup created at 16:00. The statement to recover the database looks like this:
USE [master]
GO
RESTORE DATABASE [AdventureWorks2008R2]
FROM DISK = 'D:\Backup\10:00_Full.bak' WITH NORECOVERY, REPLACE
RESTORE DATABASE [AdventureWorks2008R2]
FROM DISK = 'D:\Backup\16:00_Diff.bak' WITH RECOVERY
The aforementioned method does not allow a database using the Simple model to retrieve any data before a point in time when data was deleted (only one valid backup can be retrieved using this model).
We also have two methods for setting database recovery to the Full model.
Method 1: Use SSMS IDE (The steps are the same as the first method for setting up the Simple model).
Method 2: Use an ALTER DATABASE statement like the following:
USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY FULL WITH NO_WAIT
GO
Example application scenario for the Full model:
Note: The preceding figure is from https://sqlbak.com/academy/full-recovery-model/
Scenario description:
Now, the challenge is to retrieve the data deleted at 19:00 using the database's backup information. In other words, how can we recover the database to the status at 18:59:59. Based on all database backup information, we can follow these steps to retrieve the deleted data.
First, we need to recover the full backup file created at 10:00 and set the status to norecovery.
Second, we need to recover the differential backup file created at 16:00 and also set the status to norecovery.
Then we recover the transaction log backup file created at 18:00 and still set the status to norecovery.
Finally, we recover the transaction log backup file created at 20:00. Note that we need to specify the recovery point in time as 18:59:59 (by using the STOPAT keyword) and set the status to recovery online.
The preceding steps to retrieve data can be expressed as the following code:
USE [master]
GO
RESTORE DATABASE [AdventureWorks2008R2]
FROM DISK = 'D:\Backup\10:00_Full.bak.bak' WITH NORECOVERY, REPLACE
RESTORE DATABASE [AdventureWorks2008R2]
FROM DISK = 'D:\Backup\16:00_Diff.bak' WITH NORECOVERY
RESTORE LOG [AdventureWorks2008R2]
FROM DISK = 'D:\Backup\18:00_Log.trn' WITH NORECOVERY
RESTORE LOG [AdventureWorks2008R2]
FROM DISK = 'D:\Backup\20:00_Log.trn' WITH STOPAT = '2018-02-20 18:59:59', RECOVERY
Using the Full database recovery model, we can retrieve mistakenly deleted data by following the steps above and utilizing backup files. However, this is impossible if a database is using the Simple model.
We also have two methods for setting a database to use the Bulk-logged model.
Method 1: Use SSMS IDE (The steps are the same as the first method for setting up the Simple model).
Method 2: Use an ALTER DATABASE statement similar to:
USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY Bulk_LOGGED WITH NO_WAIT
GO
Example application scenario of the Bulk-logged model:
Note: The preceding figure is from https://sqlbak.com/academy/Bulk-logged-recovery-model/
Scenario description:
Now the challenge is to use these backup files to retrieve as much data as possible to minimize data loss. As previously described, databases using the Bulk-logged model cannot implement point-in-time recovery. Therefore, we cannot use the transaction log backup file created at 16:00. Even if we use that file, the following error occurs:
This log backup contains Bulk-logged changes. It cannot be used to stop at an arbitrary point in time.
The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.
Restore log is determining normally.
Eventually, the data that we can retrieve can at most use the transaction log backup created at 14:00. The following are the steps to retrieve the deleted data:
First, we need to recover the full backup file created at 10:00 and set the status to norecovery.
Second, we recover the transaction log backup file created at 12:00 and set the status to norecovery.
Finally, we recover the transaction log backup created 14:00 and set the status to recovery online.
The preceding procedure description can be expressed in the following code:
USE [master]
GO
RESTORE DATABASE [AdventureWorks2008R2]
FROM DISK = 'D:\Backup\10:00_Full.bak.bak' WITH NORECOVERY, REPLACE
RESTORE LOG [AdventureWorks2008R2]
FROM DISK = 'D:\Backup\12:00_Log.trn' WITH NORECOVERY
RESTORE LOG [AdventureWorks2008R2]
FROM DISK = 'D:\Backup\14:00_Log.trn' WITH RECOVERY
The steps above prove that databases using the Bulk-logged model cannot implement point-in-time recovery. Therefore, this model is usually used during the process of performing Bulk operations.
In this topic, we shared the working principles, application scenarios, and typical example application scenarios for the three recovery models in SQL Server to explore and discuss the relationship between database recovery models and backups. Now, we see that the database recovery models and backups work collaboratively to ensure data security and minimize data loss in the event of disasters.
SQL Server Best Practices: Monitoring Backup and Restore Progress
SQL Server Best Practices: Using Filegroups to Implement Cold and Hot Data Isolation Backup Solution
11 posts | 0 followers
FollowAlibaba Clouder - July 5, 2019
Cherish Wang - February 20, 2019
Alibaba Clouder - November 12, 2018
Alibaba Clouder - July 22, 2020
Alibaba Clouder - July 5, 2019
Alibaba Clouder - August 6, 2020
11 posts | 0 followers
FollowA reliable, cost-efficient backup service for continuous data protection.
Learn MoreTair is a Redis-compatible in-memory database service that provides a variety of data structures and enterprise-level capabilities.
Learn MoreProtect, backup, and restore your data assets on the cloud with Alibaba Cloud database services.
Learn MoreAlibaba Cloud provides products and services to help you properly plan and execute data backup, massive data archiving, and storage-level disaster recovery.
Learn MoreMore Posts by Cherish Wang