SQL Server备份与恢复- 恢复模型探究

1、概述

为了创建正确的备份，第一件事是为每个数据库设置正确的恢复模型。恢复模型基本上告诉 SQL Server 哪些数据要保留在事务日志文件中以及保留多长时间。根据选择的恢复模式，这还将确定您可以执行哪些类型的备份以及可以执行哪些类型的数据库还原。

2、三种恢复模式

您可以选择的三种恢复模式是：

• Full
• Simple
• Bulk-Logged

每个数据库只能有一个恢复模式，但每个数据库都可以使用不同的恢复模式，因此根据处理和备份需求，您可以为每个数据库选择合适的恢复模式。唯一的例外是必须使用“简单”恢复模型的 TempDB 数据库。

此外，可以随时更改数据库恢复模式，但这会影响您的备份链，因此最好在更改恢复模式后进行完整备份。

可以使用 T-SQL 或 SQL Server Management Studio 更改恢复模型。以下是有关如何执行此操作的示例。

使用 T-SQL 更改 AdventureWorks 数据库的“完全”恢复。

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO

使用 SSMS 更改 AdventureWorks 数据库的恢复模式。

3、完整恢复模型

完整恢复模型告诉 SQL Server 将所有事务数据保留在事务日志中，直到发生事务日志备份或事务日志被截断。其工作方式是针对 SQL Server 发出的所有事务首先进入事务日志，然后将数据写入适当的数据文件。这允许 SQL Server 回滚过程的每个步骤，以防出现错误或事务由于某种原因被取消。因此，当数据库设置为“完整”恢复模式时，由于所有事务都已保存，因此您可以进行时间点恢复，这意味着您可以恢复到事务发生之前的某个点，就像意外删除所有事务一样表中的数据。

完整恢复模式是最完整的恢复模式，只要所有备份文件可用且可读，您就可以将所有数据恢复到任何时间点。使用此模型，所有操作都被完整记录，这意味着您可以将数据库恢复到任何点。此外，如果数据库设置为完全恢复模式，您还需要发出事务日志备份，否则您的数据库事务日志将永远持续增长。

以下是您可能选择此恢复模式的一些原因：

• 数据至关重要，您希望最大限度地减少数据丢失。
• 您需要能够进行时间点恢复。
• 您正在使用数据库镜像
• 您正在使用 Always On 可用性组

当数据处于“完整”恢复模式时可以运行的备份类型：

• 完成备份
• 差异备份
• 文件和/或文件组备份
• 部分备份
• 仅复制备份
• 事务日志备份

使用 T-SQL 设置 SQL Server 完整恢复模型

ALTER DATABASE dbName SET RECOVERY recoveryOption
GO

示例：将 AdventureWorks 数据库更改为“完整”恢复模式

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO

使用 Management Studio 设置 SQL Server 完整恢复模式

• 右键单击数据库名称并选择属性
• 转到选项页面
• 在恢复模式下选择“完整”
• 点击“确定”保存

4、简单恢复模型

它为您提供了一个简单的备份，可用于在发生故障或需要将数据库恢复到另一台服务器时替换整个数据库。使用这种恢复模式，您可以进行完整备份（整个副本）或差异备份（自上次完整备份以来的任何更改）。使用此恢复模式，您将面临自上次备份完成以来的任何故障，因为您只能将数据恢复到备份发生的时间点。

“简单”恢复模型是 SQL Server 最基本的恢复模型。每个事务仍然写入事务日志，但是一旦事务完成并且数据已写入数据文件，事务日志文件中使用的空间可以被新事务重用。由于此空间被重复使用，因此无法进行时间点恢复，因此最近的还原点将是完整备份或已完成的最新差异备份。此外，由于事务日志中的空间可以重复使用，事务日志不会像“完全”恢复模型中提到的那样永远增长。

以下是您可能选择此恢复模式的一些原因：

• 您的数据并不重要，可以轻松地重新创建
• 该数据库仅用于测试或开发
• 数据是静态的，不会改变
• 自上次备份以来丢失任何或所有事务不是问题
• 数据是派生的，可以很容易地重新创建

当数据处于“简单”恢复模式时可以运行的备份类型：

• 完成备份
• 差异备份
• 文件和/或文件组备份
• 部分备份
• 仅复制备份

使用 T-SQL 设置 SQL Server 简单恢复模型

ALTER DATABASE dbName SET RECOVERY recoveryOption
GO

示例：将 AdventureWorks 数据库更改为“简单”恢复模式

ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
GO

使用 Management Studio 设置 SQL Server 简单恢复模型

• 右键单击数据库名称并选择属性
• 转到选项页面
• 在恢复模式下选择“简单”
• 点击“确定”保存

5、大容量日志恢复模型

在此模型中，某些批量操作（例如 BULK INSERT、CREATE INDEX、SELECT INTO 等）并未完全记录在事务日志中，因此不会在事务日志中占用太多空间。

使用“批量记录”恢复模型的优点是，如果您正在执行大量批量操作，您的事务日志不会变得那么大，并且只要您的最后一个事务日志备份没有，它仍然允许您进行时间点恢复包括批量操作。如果未运行批量操作，则此恢复模式与完全恢复模式的工作方式相同。需要注意的一点是，如果您使用这种恢复模式，您还需要发出事务日志备份，否则您的数据库事务日志将继续增长。

以下是您可能选择此恢复模式的一些原因：

• 数据很关键，您希望最大程度地减少数据丢失，但又不想记录大批量操作
• 与正常处理相比，批量操作在不同时间完成。
• 您仍然希望能够恢复到某个时间点

当数据处于“批量记录”恢复模式时，您可以运行的备份类型：

• 完成备份
• 差异备份
• 文件和/或文件组备份
• 部分备份
• 仅复制备份
• 事务日志备份

使用 T-SQL 设置 SQL Server 大容量日志恢复模型

ALTER DATABASE dbName SET RECOVERY recoveryOption
GO

示例：将 AdventureWorks 数据库更改为“批量记录”恢复模式

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED
GO

使用 Management Studio 设置 SQL Server 批量日志恢复模型

• 右键单击数据库名称并选择属性
• 转到选项页面
• 在恢复模式下选择“批量记录”
• 点击“确定”保存

6、大容量日志恢复模型