SQL Server Best Practices: Database Recovery Models and Backups

  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.

  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.

  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.

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.

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:

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

Setting up the Full model

We also have two methods for setting database recovery to the Full model.

USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY FULL WITH NO_WAIT
GO

Application Examples

Example application scenario for the Full model:

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

Setting up the Bulk-Logged Model

We also have two methods for setting a database to use the Bulk-logged model.

USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY Bulk_LOGGED WITH NO_WAIT
GO

Application Examples

Example application scenario of the Bulk-logged model:

  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
This log backup contains Bulk-logged changes. It cannot be used to stop at an arbitrary point in time.
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

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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

4.97K Followers

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com