14.2 SQL Server恢复模式


 

SQLServer 恢复模式(Recovery Model)

 

目录

 

简介

恢复模式控制以下内容：

  • 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。
 
分类: SQL Server

posted on 2025-04-21 17:00  漫思  阅读(70)  评论(0)    收藏  举报

导航