如何使用 T-SQL 更改 SQL Server 数据库的恢复模式

介绍 SQL Server 的三种数据库恢复模式(简单、完整、大容量日志),详解如何使用 T-SQL 的 ALTER DATABASE SET RECOVERY 语句切换恢复模式,以及启动日志链与维护事务日志的核心注意事项。

发布于

在 SQL Server 中,数据库恢复模式(Recovery Model)决定了事务日志记录的详细程度以及数据库支持的备份与还原策略。SQL Server 提供了三种恢复模式:

  • SIMPLE(简单恢复模式):每次检查点自动截断不活动的事务日志,不支持事务日志备份。仅支持恢复到完整或差异备份的时刻,无法进行时间点还原(Point-in-Time Recovery)。
  • FULL(完整恢复模式):完整记录所有事务操作。支持事务日志备份以及精确到特定时间点的故障恢复,常用于生产高可用环境。
  • BULK_LOGGED(大容量日志恢复模式):对某些大容量操作(如 BULK INSERT、SELECT INTO、索引重建等)进行最小日志记录,以降低日志空间消耗,通常仅在执行大批量导入时临时使用。

本文介绍如何使用 T-SQL 查询并修改数据库的恢复模式。

语法

修改恢复模式需要具备目标数据库的 ALTER 权限,其基本语法如下:

USE master;
GO

ALTER DATABASE <database_name> 
SET RECOVERY { FULL | BULK_LOGGED | SIMPLE };
GO

操作示例

假设我们有一个名为 PetHotel 的测试数据库。

1. 查询当前恢复模式

在修改前,先查询 sys.databases 系统视图确认当前设置:

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

输出:

+----------+-----------------------+
| name     | recovery_model_desc   |
|----------+-----------------------|
| PetHotel | SIMPLE                |
+----------+-----------------------+

当前 PetHotel 处于简单恢复模式(SIMPLE)。

2. 更改为完整恢复模式

执行 ALTER DATABASE 语句将其切换为完整恢复模式(FULL):

USE master;  
GO

ALTER DATABASE PetHotel 
SET RECOVERY FULL;
GO

命令执行成功后,再次验证结果:

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

输出:

+----------+-----------------------+
| name     | recovery_model_desc   |
|----------+-----------------------|
| PetHotel | FULL                  |
+----------+-----------------------+

数据库已成功切换至完整恢复模式。


运维关键注意事项

在生产环境中切换恢复模式时,必须遵守以下运维最佳实践:

[!IMPORTANT] 启动日志链(Log Chain):从 SIMPLE 切换到 FULL 或 BULK_LOGGED 后,完整恢复模式并不会立即完全生效。您必须立即对该数据库执行一次完整备份(Full Backup)或差异备份(Differential Backup)。只有这样才能启动日志链,后续的事务日志备份作业才能正常运行。

[!WARNING] 日志备份作业与磁盘暴涨:

  1. 切换至 FULL 模式后,事务日志将持续增长且不会自动截断,必须立即配置定期的事务日志备份作业(例如每 15 分钟一次),否则事务日志文件(.ldf)会很快耗尽服务器磁盘空间。
  2. 若将数据库切换为 SIMPLE 模式,请务必暂停或禁用现有的事务日志备份作业,因为简单模式下执行日志备份会直接报错。
  3. BULK_LOGGED 模式仅建议在大规模数据加载期间临时开启,批量操作完成后应立即切回 FULL 模式并执行事务日志备份。