从SQL Server事务日志中恢复数据

If this is your first visit to this SQL Server Transaction Log series, I recommend you first review the previous articles (see the TOC below), in order not to be familiar with the concepts that we will discuss in this article. Reviewing the previous articles, you will have a good idea about the following:

如果这是您第一次访问此SQL Server事务日志系列，建议您先阅读以前的文章（请参阅下面的目录 ），以免熟悉我们将在本文中讨论的概念。 阅读前面的文章，您将对以下内容有个好主意：

• The internal structure of the SQL Server Transaction Log
  SQL Server事务日志的内部结构
• the three recovery model types, Full, Simple and Bulk-Logged, that specifies how the transactions will be written to the SQL Server Transaction Log file
  三种恢复模型类型，完全，简单和大容量日志记录，用于指定如何将事务写入SQL Server事务日志文件
• the relationship between the SQL Server Transaction Log and the different types of high availability and disaster recovery solutions
  SQL Server事务日志与不同类型的高可用性和灾难恢复解决方案之间的关系
• how to manage and monitor the SQL Server Transaction Log file growth, the different operations that can be performed on the Transaction Log, such as the log backup, shrink and truncate operations
  如何管理和监视SQL Server事务日志文件的增长，可以在事务日志上执行的不同操作，例如日志备份，收缩和截断操作
• and finally, the list of best practices that should be performed by the database administrators in order to keep the SQL Server Transaction Log in healthy state
  最后，数据库管理员应执行的最佳做法列表，以使SQL Server事务日志保持正常状态

总览 (Overview)

This article will be a continuation for the previous article, in which we showed the important role that the SQL Server Transaction Log plays in keeping the database in a consistent state and recovering the corrupted database or mistakenly modified table to a specific point in time.

本文是上一篇文章的继续，其中我们展示了SQL Server事务日志在使数据库保持一致状态以及将损坏的数据库或错误地修改的表恢复到特定时间点方面所起的重要作用。

Designing a proper backup and recovery plan, that includes the different types of backup operations that are scheduled in a way that achieves the agreed RTO and RPO measure, will minimize and for some level prevent any data loss from your database by restoring the database to the correct point in time. But what if this plan is not configured well and the disaster occurred?

设计适当的备份和恢复计划，其中包括以达成一致的RTO和RPO措施的方式计划的不同类型的备份操作，这将最大限度地减少数据库并在某种程度上通过将数据库还原到数据库而防止任何数据丢失。正确的时间点。 但是，如果此计划配置不当而灾难发生了怎么办？

The SQL Server Transaction Log plays also an important role in recovering deleted or modified data if you mistakenly perform a DELETE or UPDATE operation with the wrong condition, or badly without filtration condition. This can be achieved by listening to the records stored inside in this black box that is called SQL Server Transaction Log file. The events that are written to the SQL Server Transaction Log file, without any additional configuration required from the database administrator side, includes the different types of DML operations, such as INSERT, UPDATE and DELETE statements, and DDL operations, such as CREATE and DROP statements.

如果您以错误的条件错误地执行了DELETE或UPDATE操作，或者严重地没有过滤条件，则SQL Server事务日志在恢复已删除或已修改的数据中也起着重要的作用。 这可以通过侦听此黑盒中存储的称为SQL Server事务日志文件的记录来实现。 无需数据库管理员方面的任何其他配置即可写入SQL Server事务日志文件的事件包括不同类型的DML操作（例如INSERT，UPDATE和DELETE语句）以及DDL操作（例如CREATE和DROP）陈述。

入门 (Getting Started)

To compare the different methods that we can use to recover data from the SQL Server Transaction Log file, we will create a new database with a single table, using the script below:

为了比较可用于从SQL Server事务日志文件中恢复数据的不同方法，我们将使用以下脚本使用单个表创建一个新数据库：

CREATE DATABASE RecoverFromTransactionLog
GO
USE RecoverFromTransactionLog
GO
CREATE TABLE [dbo].[Employee_Main](
  [Emp_ID] [int] IDENTITY(1,1) NOT NULL,
  [EMP_FirsrName] [varchar](50) NULL,
  [EMP_LastName] [varchar](50) NULL,
  [EMP_BirthDate] [datetime] NULL,
  [EMP_PhoneNumber] [varchar](50) NULL,
  [EMP_Address] [varchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
  [Emp_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

After creating the database and the table, we will fill that table with 30 records, using the INSERT INTO T-SQL statements below:

创建数据库和表之后，我们将使用以下INSERT INTO T-SQL语句用30条记录填充该表：

USE [RecoverFromTransactionLog]
GO
INSERT INTO [dbo].[Employee_Main] VALUES ('AAA','BBB','1980-10-15','98542224','ABCDE')
GO 10
INSERT INTO [dbo].[Employee_Main] VALUES ('CCC','DDD','1985-08-07','98543334','FGHIJ')
GO 10
INSERT INTO [dbo].[Employee_Main] VALUES ('EEE','FFF','1989-04-06','98544454','KLMNO')
GO 10

Having no backup taken from the database yet, how could we recover the table’s data if the below UPDATE statement is executed over that table with no WHERE clause used to filter the data to be modified?

还没有从数据库中获取备份，如果在该表上执行以下UPDATE语句而没有用于过滤要修改的数据的WHERE子句，那么如何恢复该表的数据呢？

使用内置方法 (Using Built-in Methods)

fn_dblog (fn_dblog)

The content of the SQL Server Transaction Log can be re