SQL 恢复的三个阶段：分析阶段，重做阶段，最后是撤销阶段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2391399/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Three phases of recovery : the analysis phase, the redo phase, and finally the undo phase
提问by user186246
SQL Server database recovery consists of three phases: the analysis phase, the redo phase, and finally the undo phase.
SQL Server 数据库恢复包括三个阶段：分析阶段、重做阶段和最后的撤消阶段。
Can anybody please explain clearly or give a good link on these three phases of recovery??
有人可以解释清楚或提供有关这三个恢复阶段的良好链接吗？
回答by Adriaan Stander
From Understanding How Restore and Recovery of Backups Work in SQL Server
A restore is a multiphase process. The possible phases of a restore include the data copy, redo (roll forward), and undo (roll back) phases:
- The data copy phase involves copying all the data, log, and index pages from the backup media of a database to the database files.
- The redo phase applies the logged transactions to the data copied from
the backup to roll forward that data
to the recovery point. At this point, a database typically has uncommitted
transactions and is in an unusable
state. In that case, an undo phase is required as part of recovering the
database.- The undo phase, which is the first part of recovery, rolls back any
uncommitted transactions and makes
the database available to users.
After the roll back phase, subsequent backups cannot be restored.
恢复是一个多阶段的过程。还原的可能阶段包括数据复制、重做（前滚）和撤消（回滚）阶段：
- 数据复制阶段涉及将所有数据、日志和索引页从数据库的备份介质复制到数据库文件。
- 重做阶段将记录的事务应用于从
备份复制的数据，以将该数据前滚到
恢复点。此时，数据库通常具有未提交的
事务并且处于不可用
状态。在这种情况下，需要一个撤销阶段作为恢复
数据库的一部分。- 撤消阶段是恢复的第一部分，它回滚任何
未提交的事务并使
数据库可供用户使用。
在回滚阶段之后，无法恢复后续备份。
回答by Kundan Dasange
The Phases of Recovery:
恢复阶段：
A restore is a multiphase process. The possible phases of a restore include the data copy, redo (roll forward), and undo (roll back) phases:
恢复是一个多阶段的过程。还原的可能阶段包括数据复制、重做（前滚）和撤消（回滚）阶段：
- The data copy phaseinvolves copying all the data, log, and index pages from the backup media of a database to the database files.
- The redo phaseapplies the logged transactions to the data copied from the backup to roll forward that data to the recovery point. At this point, a database typically has uncommitted transactions and is in an unusable state. In that case, an undo phase is required as part of recovering the database.
- The undo phase, which is the first part of recovery, rolls back any uncommitted transactions and makes the database available to users. After the roll back phase, subsequent backups cannot be restored.
- 数据复制阶段涉及将所有数据、日志和索引页从数据库的备份介质复制到数据库文件。
- 重做阶段将记录的事务应用于从备份复制的数据，以将该数据前滚到恢复点。此时，数据库通常具有未提交的事务并且处于不可用状态。在这种情况下，需要一个撤销阶段作为恢复数据库的一部分。
- 撤消阶段是恢复的第一部分，它回滚所有未提交的事务并使数据库可供用户使用。在回滚阶段之后，无法恢复后续备份。
For more information you can refer to the following links:
有关更多信息，您可以参考以下链接：
https://technet.microsoft.com/en-us/library/ms191455(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms191455(v=sql.105).aspx
回答by gvo
You might find this document interesting : Simple overview of the SQL Server Recovery Process
您可能会发现此文档很有趣：SQL Server 恢复过程的简单概述
Phases of recovery
the recovery algorithm has 3 phases based around the last checkpoint in the transaction log.
Phase 1: Analysis. Starts at the last checkpoint in transaction log. This pass determines and constructs a dirty page table (DPT) consisting of pages that might be dirty at the time SQL Server stopped. An active transaction table is built of the uncommitted transactions at the time of the SQL Server stopped also.
Phase 2: Redo. This phase returns the database to the state at the time the SQL service stopped. Starting point for this forward passbeing the oldest uncommitted transaction. The mininum Log Sequence name (each log record is labelled with an LSN) in the DPT is the first time SQL Server expects to have to redo an operation on a page, redoing the logged operations starting right back at the oldest open transaction so that the neccessary locks can be aquired.
Phase 3: Undo: Here the list of active transaction (uncommitted at the time SQL Server stoopped) which where indentified in Phase 1 are rolled back individually. SQL Server follows the links between entries in the transaction log for each transaction. Any transaction that was not committed at the time SQL Server stopped is undone.
恢复阶段
恢复算法有 3 个阶段，基于事务日志中的最后一个检查点。
第一阶段：分析。从事务日志中的最后一个检查点开始。此过程确定并构造一个脏页表 (DPT)，其中包含在 SQL Server 停止时可能脏的页。活动事务表也是由 SQL Server 停止时未提交的事务构建的。
阶段 2：重做。此阶段将数据库恢复到 SQL 服务停止时的状态。此前向传递的起点是最旧的未提交事务。DPT 中的最小日志序列名称（每个日志记录都标有 LSN）是 SQL Server 期望必须在页面上重做操作的第一次，从最旧的打开事务开始重做已记录的操作，以便可以获取必要的锁。
阶段 3：撤消：此处将在阶段 1 中确定的活动事务列表（在 SQL Server 停止时未提交）单独回滚。SQL Server 遵循每个事务的事务日志中条目之间的链接。在 SQL Server 停止时未提交的任何事务都将撤消。
Recovery can be done when you restore the database, but it is also done at the startup of the database (crash recovery).
恢复可以在恢复数据库时完成，但也可以在数据库启动时完成（崩溃恢复）。
回答by Hari Krishna S
Regarding the difference between redo and commit: Commit simply means you have generated the log record indicating the completion of a transaction and flushed it to disk (it is now a persisted data). This does not mean that the data you changed in the transaction is written to disk. The data will get flushed async depending on the pressure on the buffer and checkpoints.
关于重做和提交的区别：提交只是意味着您已经生成了指示事务完成的日志记录并将其刷新到磁盘（它现在是一个持久化数据）。这并不意味着您在事务中更改的数据会写入磁盘。根据缓冲区和检查点的压力，数据将被异步刷新。
So, when SQL crashes and restarts you have just lost all the data changes that was not flushed. Hence, you now have to redo all the log records since the last flush. This is the redo phase (phase 2) in recovery. You database will not be available until this is done.
因此，当 SQL 崩溃并重新启动时，您刚刚丢失了所有未刷新的数据更改。因此，您现在必须重做自上次刷新以来的所有日志记录。这是恢复中的重做阶段（阶段 2）。在完成此操作之前，您的数据库将不可用。
