SQL Server事务日志和恢复模型

In the previous articles of this series (see TOC at bottom), we discussed the main concept of the SQL Server transaction, the internal architecture of the SQL Server Transaction Log and finally the Virtual Log File and the best practices in managing the VLFs. In this article, we will go through the relationship between the SQL Server Transaction Log and the three database recovery models.

在本系列的前几篇文章（请参阅底部的TOC）中，我们讨论了SQL Server事务的主要概念，SQL Server事务日志的内部体系结构以及虚拟日志文件以及管理VLF的最佳实践。 在本文中，我们将介绍SQL Server事务日志和三种数据库恢复模型之间的关系。

恢复模型概述 (Recovery Models Overview)

A recovery model is a database property that defines how the SQL Server Engine treats the database transaction logs including

恢复模型是一种数据库属性，它定义SQL Server Engine如何处理数据库事务日志，包括

• where it specifies how these transactions will be logged and saved to the SQL Server transaction log file
  它指定如何记录这些事务并将其保存到SQL Server事务日志文件中
• the types of backup and restore operations that can be performed on the database
  可以在数据库上执行的备份和还原操作的类型
• the high availability or disaster recovery solutions that are supported on the database
  数据库支持的高可用性或灾难恢复解决方案
• and the recovery point in time that we can recover the database to
  以及我们可以将数据库恢复到的恢复时间点

There are three recovery model types in SQL Server: Full, Bulk-logged and Simple recovery models. To get an in-depth understand for these recovery models, we will discuss each recovery model individually with its relationship with the SQL Server transaction log.

SQL Server中有三种恢复模型类型： 完整，批量记录和简单恢复模型。 为了深入了解这些恢复模型，我们将分别讨论每个恢复模型及其与SQL Server事务日志的关系。

The recovery model of a newly created database will be the same as the recovery model of the model system database. You can change the recovery model of an existing database from the Options page of the database properties window, using the SQL Server Management Studio.

新创建的数据库的恢复模型将与模型系统数据库的恢复模型相同。 您可以使用SQL Server Management Studio从数据库属性窗口的“ 选项”页面更改现有数据库的恢复模型。

To do that, right-click on the database and choose the Properties options. From the Database Properties window, move to the Options page and choose the new recovery model of your database from the Recovery Model drop-down list, as shown below:

为此，右键单击数据库，然后选择“属性”选项。 在“ 数据库属性”窗口中，移至“ 选项”页面，然后从“恢复模型”下拉列表中选择数据库的新恢复模型，如下所示：

You can also change the recovery model of the database using the ALTER DATABASE T-SQL command below:

您还可以使用下面的ALTER DATABASE T-SQL命令更改数据库的恢复模型：

ALTER DATABASE [ApexSQL_Log_Test] SET RECOVERY SIMPLE 
GO

简单恢复模型 (Simple Recovery Model)

When the database is configured with Simple recovery model, the SQL Server Engine stores the SQL transaction logs in the transaction log file for a short time while