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