了解SQL Server数据库恢复模型

A recovery model is a database configuration option that determines the type of backup that one could perform, and provides the ability to restore the data or recover it from a failure.

恢复模型是一种数据库配置选项，用于确定可以执行的备份类型，并提供恢复数据或从故障中恢复数据的功能。

The recovery model decides how the transaction log of a database should be maintained and protects the data changes in a specific sequence, which may later be used for a database restore operation.

恢复模型决定应如何维护数据库的事务日志，并按特定顺序保护数据更改，以后可以将其用于数据库还原操作。

恢复模型的类型 (Types of recovery models)

All SQL Server database backup, restore, and recovery operations are based on one of three available recovery models:

所有SQL Server数据库备份，还原和恢复操作均基于以下三种可用恢复模型之一：

• SIMPLE
  简单
• FULL
  充分
• BULK_Logged
  BULK_已记录

简单 (SIMPLE )

The SIMPLE recovery model is the simplest among the available models. It supports full, differential, and file level backups. Transaction log backups are not supported. The log space is reused whenever the SQL Server background process checkpoint operation occurs. The inactive portion of the log file is removed and is made available for reuse.

SIMPLE恢复模型是可用模型中最简单的模型。 它支持完整，差异和文件级备份。 不支持事务日志备份。 每当发生SQL Server后台进程检查点操作时，便会重用日志空间。 日志文件的非活动部分将被删除，并可供重用。

Point-in-time and page restore are not supported, only the restoration of the secondary read-only file is supported.

不支持时间点和页面还原，仅支持辅助只读文件的还原。

Reasons to choose the simple database recovery model

选择简单数据库恢复模型的原因

1. Most suited for Development and Test databases
   最适合开发和测试数据库
2. Simple reporting or application database, where data loss is acceptable
   简单的报告或应用程序数据库，可以接受数据丢失
3. The point-of-failure recovery is exclusively for full and differential backups
   故障点恢复专门用于完整备份和差异备份
4. No administrative overhead
   没有管理费用

It supports:

它支持：

1. Full backup
   完整备份
2. Differential backup
   差异备份
3. Copy-Only backup
   仅复制备份
4. File backup
   文件备份
5. Partial backup
   部分备份

充分 (FULL)

In this recovery model, all the transactions (DDL (Data Definition Language) + DML (Data Manipulation Language)) are fully recorded in the transaction log file. The log sequence is unbroken and is preserved for the databases restore operations. Unlike the Simple recovery model, the transaction log file is not auto-truncated during CHECKPOINT operations.

在此恢复模型中，所有事务（DDL（数据定义语言）+ DML（数据操作语言））都完全记录在事务日志文件中。 日志序列是不间断的，并保留用于数据库还原操作。 与简单恢复模型不同，在CHECKPOINT操作过程中不会自动截断事务日志文件。

All restore operations are supported, including point-in-time restore, page restore and file restore.

支持所有还原操作，包括时间点还原，页面还原和文件还原。

Reasons to choose the full database recovery model:

选择完整数据库恢复模型的原因：

1. Supporting mission critical applications
   支持关键任务应用
2. Design High Availability solutions
   设计高可用性解决方案
3. To facilitate the recovery of all the data with zero or minimal data loss
   促进零丢失或最小数据丢失的所有数据的恢复
4. If the database designed to have multiple filegroups, and you want to perform a piecemeal restore of read/write secondary filegroups and, optionally, read-only filegroups.
   如果数据库设计为具有多个文件组，并且您要对读/写辅助文件组以及（可选）只读文件组执行逐段还原。
5. Allow arbitrary point-in-time restoration
   允许任意时间点还原
6. Restore individual pages
   恢复单个页面
7. Incur high administration overhead
   产生高昂的管理费​​用

It supports all type of following backups

它支持所有类型的以下备份

1. Full backup
   完整备份
2. Differential backup
   差异备份
3. Transaction log backup
   交易日志备份
4. Copy-Only backup
   仅复制备份
5. File and/or file-group backup
   文件和/或文件组备份
6. Partial backup
   部分备份

BULK_LOGGED (BULK_LOGGED)

It’s a special purpose database configuration option and it works similar to FULL recovery model except that certain bulk operations can be minimally logged. The transaction log file uses a technique known as minimal logging for bulk operations. The catch is that it’s not possible to restore specific point-in-time data.

这是一个特殊用途的数据库配置选项，除了可以完全记录某些批量操作之外，它的工作方式与FULL恢复模型相似。 事务日志文件使用称为批量操作的最小日志记录的技术。 问题是不可能恢复特定的时间点数据。

Reasons to choose the bulk logged recovery model:

选择批量记录的恢复模式的原因：

1. Use minimal logging technique to prevent log file growth
   使用最少的日志记录技术来防止日志文件增长
2. If the database is subjected to periodic bulk operations
   如果数据库进行定期批量操作

It supports all types of backups:

它支持所有类型的备份：

1. Full backup
   完整备份
2. Differential backup
   差异备份
3. Transaction log backup
   交易日志备份
4. Copy-Only backup
   仅复制备份
5. File and/or file-group backup
   文件和/或文件组备份
6. Partial backup
   部分备份

事务日志内部 (Transaction log internals)

It’s worth taking the time to understand the internals of the SQL Server transaction log.

值得花时间来了解SQL Server事务日志的内部。

1. Whenever there is a transaction (DDL and DML) the details of every operation is logged in the transaction log file
   每当有事务（DDL和DML）时，每个操作的详细信息都会记录在事务日志文件中
2. The transaction log backup ensures transactional durability and data consisten