Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog SQL Server Best Practices: Database Recovery Models and Backups

SQL Server Best Practices: Database Recovery Models and Backups

In this article of the SQL Server best practices series, we will discuss the about three database recovery models and their application scenarios.

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:

  1. Simple
  2. Full
  3. Bulk-logged

Simple Recovery Model

In the Simple recovery model, database transaction logs are cleared along with the Checkpoint or Backup operation to minimize transaction logs.

Principles

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:

  1. Database log backups cannot be implemented.
  2. Databases based on the Simple model cannot implement point-in-time recovery.
  3. Data can at most be recovered to the last backup file (either full backup or differential backup) and cannot be recovered to the latest availability status.

Application Scenarios

According to the aforementioned principles of the Simple database recovery model, we can easily find applicable scenarios for the Simple model, including:

  1. Non-crucial data (for example, log information) is stored in databases.
  2. Databases do not require point-in-time recovery at any time and in any cases.
  3. Loss of partial databases is tolerable in the event of database disasters.
  4. Data in a database has a very low change frequency.
  5. Databases do not require high availability (HA) in a foreseeable period (such as Database Mirroring, AlwaysOn, and Log Shipping).

Full Recovery Model

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.

Principles

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:

  1. Database logs can be backed up.
  2. Point-in-time recovery can be implemented.
  3. Data can be recovered to a point in time very close to a disaster occurrence time point to minimize data loss.

Application Scenarios

Now that we have described the Full model, let us take a look at applicable scenarios for the Full model, including:

  1. Critical business data stored in databases (such as order information and payment information).
  2. Data with very high security requirements, which, if lost, must be retrieved to the greatest extent possible at any time and in all cases.
  3. Very little data loss is acceptable in the event of disasters.
  4. Very high database HA is required (for example, high requirements on Database Mirroring or Alwayson).
  5. Point-in-time recovery of databases is required.
  6. Database recovery per page is required.

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.

Bulk-Logged Recovery Model

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.

Principles

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.

Application Scenarios

Based on Bulk-logged model principles, applicable scenarios include:

  1. Bulk Imports operations, such as BCP, Bulk INSERT and INSERT INTO... SELECT
  2. SELECT INTO operations
  3. Index-related operations: CREATE/DROP INDEX, ALTER INDEX REBUILD or DBCC DBREINDEX
  4. The most common application scenario for the Bulk-logged model is switching to Bulk-logged before a Bulk operation and then switching back to Full after the Bulk operation.

Setting up the Simple model

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.

1

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

Application Examples

The following figure shows an example scenario of the Simple model:

2

Note: The preceding figure is from https://sqlbak.com/academy/simple-recovery-model/

In this example, the following events took place

  1. 10:00 and 22:00: A full backup was performed on the database
  2. 16:00: A differential backup was performed on the database
  3. 19:00: Some crucial data was mistakenly deleted

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).

Setting up the Full 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

Application Examples

Example application scenario for the Full model:

3

Note: The preceding figure is from https://sqlbak.com/academy/full-recovery-model/

Scenario description:

  1. 10:00 and 22:00: A full backup was performed on the database
  2. 16:00: A differential backup was performed on the database
  3. 12:00, 14:00, 18:00, and 20:00: Transaction log backups were performed on the database
  4. 19:00: A disaster occurred, and some crucial data was mistakenly deleted from the database

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.

Setting up the Bulk-Logged 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

Application Examples

Example application scenario of the Bulk-logged model:

4

Note: The preceding figure is from https://sqlbak.com/academy/Bulk-logged-recovery-model/

Scenario description:

  1. 10:00: A full backup was performed on the database at
  2. 12:00, 14:00, 16:00, and 18:00: Transaction log backups were performed on the database. The log backup (marked in yellow) at 16:00 was performed after the model had been changed to Bulk-logged
  3. 20:00: A differential backup was performed on the database
  4. 14:30: The database model was changed to Bulk-logged
  5. 15:00: A disaster occurred, and some important data was mistakenly deleted

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.

Summary

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.

Database Storage dts Data Backup SQL Server Database Backup Disaster Recovery ApsaraDB for SQL Server
0 0 0
Share on

Read previous post:

SQL Server Best Practices: Monitoring Backup and Restore Progress

Read next post:

SQL Server Best Practices: Using Filegroups to Implement Cold and Hot Data Isolation Backup Solution

Cherish Wang

11 posts | 0 followers

You may also like

Comments

Cherish Wang

11 posts | 0 followers

Related Products

More Posts by Cherish Wang

See All