14.2 SQL Server恢复模式

SQLServer 恢复模式(Recovery Model)

目录

• SQLServer 恢复模式(Recovery Model)
  • 简介
  • 查看数据库的恢复模式
  • 更改恢复模式
  • 简单(SIMPLE)恢复模式
  • 完整(FULL)恢复模式
  • 大容量日志(BULK_LOGGED)恢复模式
  • 总结

简介

恢复模式控制以下内容：

• SQL Server如何记录数据库的事务。
• 数据库的事务日志是否需要备份。
• 什么类型的恢复操作可用于恢复数据库。

SQL Server提供了三种恢复模式

• 简单
• 完整
• 大容量日志

创建新数据库时，SQL Server使用model数据库设置新数据库的默认恢复模式。

先创建一个新数据库HR用于示例:

CREATE DATABASE HR;

然后在HR数据库创建一张新表People

USE HR

CREATE TABLE People (
  Id int IDENTITY PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL
);

然后再插入几行：

INSERT INTO People (FirstName, LastName)
  VALUES ('John', 'Doe'),
  ('Jane', 'Doe'),
  ('Upton', 'Luis'),
  ('Dach', 'Keon');

完整代码：

CREATE DATABASE HR;
GO

USE HR;

CREATE TABLE People (
  Id int IDENTITY PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL
);


INSERT INTO People (FirstName, LastName)
  VALUES ('John', 'Doe'),
  ('Jane', 'Doe'),
  ('Upton', 'Luis'),
  ('Dach', 'Keon');

SELECT * FROM People;

查看数据库的恢复模式

查看HR数据库的恢复模式

SELECT
  name,
  recovery_model_desc
FROM master.sys.databases
WHERE name = 'HR';

可见，人力资源（HR）数据库具有完整恢复模式。

要查看当前服务器中所有数据库的恢复模型，使用以下查询：

SELECT
  name,
  recovery_model_desc
FROM master.sys.databases
ORDER BY name;

更改恢复模式

语法：

ALTER DATABASE database_name 
SET RECOVERY recovery_model;

• 首先指定数据库名
• 然后在SET RECOVERY后面指定模式，可以是SIMPLE, FULL, 和 BULK_LOGGED

比如将HR数据库的恢复模式从FULL更改为SIMPLE：

ALTER DATABASE HR
SET RECOVERY SIMPLE;

简单(SIMPLE)恢复模式

在SIMPLE恢复模式中，SQL Server在每个检查点从事务日志文件中删除事务日志。这导致事务日志文件相对较小。

此外，在SIMPLE恢复模式中，事务日志不存储事务记录。因此，将无法使用高级备份策略来最大限度地减少数据丢失。

在实践中，可以使用SIMPLE恢复模式来恢复可以从其他源（如数据库）重新加载的数据库，以便进行报告。

完整(FULL)恢复模式

在完整恢复模式中，SQL Server将事务日志保存在事务日志文件中，直到执行BACKUP LOG语句。换句话说，BACKUP LOG语句从事务日志文件中删除事务日志。

如果不经常运行BACKUP LOG语句，SQL Server将保留事务日志文件中的所有事务日志，直到事务日志文件已满且数据库不可访问。这就是为什么需要定期运行BACKUP LOG语句以防止事务日志文件满。

简而言之，FULL恢复模型允许在任何时间点恢复数据库。

大容量日志(BULK_LOGGED)恢复模式

BULK_LOGGED恢复模式的行为与FULL恢复模型几乎相同，但是大容量日志操作除外。例如，在事务日志文件中简要描述了将平面文件插入到表中的BULK INSERT。

BULK_LOGGED恢复模式不允许在任何时间点恢复数据库。
BULK_LOGGED恢复的实际场景如下：

• 在定期加载数据之前，将恢复模式设置为BULK_LOGGED
• 将数据加载到数据库中
• 完成数据加载后，将恢复模式设置回FULL
• 备份数据库

下表显示了所有恢复模式的特性：

恢复模式	描述	数据丢失	时间点恢复
简单	无日志备份	最新备份之后的更改不受保护。 在发生灾难时，这些更改必须重做	只能恢复到备份结束。
完整	需要日志备份	通常没有	如果备份在接近特定的时点完成，则可以恢复到该时点。
大容量日志	需要日志备份	如果自最近一次日志备份以来日志已损坏或发生了大容量日志操作，则必须重做自上次备份以来的更改。否则，不会丢失任何工作。	可以恢复到任何备份的末尾。不支持时间点恢复。

总结

• 恢复模式是数据库的属性，用于控制事务的日志记录方式。
• 恢复模式可以是以下类型之一：SIMPLE、FULL和BULK_LOGGED。
• 对于其数据可以从其他源重新加载的数据库，使用SIMPLE恢复模式。
• 如果要在任何时间点恢复数据库，使用FULL恢复模式。
• 将BULK_LOGGED恢复模式用于批量日志操作，如BULK INSERT。