SQL Server 管理系列（第三期）：备份与恢复——数据安全的最后防线

绩隐金
0 阅读10分钟

SQL Server 管理系列（第三期）：备份与恢复——数据安全的最后防线

前两期我们完成了安装配置和安全加固。这一期，我们来到 DBA 最核心的职责——备份与恢复。你是否遇到过：硬盘故障导致数据丢失？误删数据后才发现备份不可用？日志文件无限增长撑爆磁盘？RPO（恢复点目标）和 RTO（恢复时间目标）不知道如何设计？这一期，我们从原理到实战，构建一套可靠、可恢复的备份体系。

一、备份类型深度解析

1.1 三种备份类型对比

备份类型备份内容大小恢复复杂度适用场景
完整备份整个数据库最大最简单每周一次
差异备份上次完整备份后的所有变更中等需先恢复完整备份每天一次
日志备份上次日志备份后的所有事务日志最小需完整+差异+日志链每小时/每15分钟
-- 查看数据库恢复模式（日志备份的前提）
SELECT name, recovery_model_desc 
FROM sys.databases

-- 完整备份
BACKUP DATABASE YourDB 
TO DISK = 'G:\Backup\Full\YourDB_Full_20240101.bak'
WITH INIT, COMPRESSION, STATS = 10

-- 差异备份
BACKUP DATABASE YourDB 
TO DISK = 'G:\Backup\Diff\YourDB_Diff_20240102.bak'
WITH DIFFERENTIAL, INIT, COMPRESSION

-- 日志备份
BACKUP LOG YourDB 
TO DISK = 'G:\Backup\Log\YourDB_Log_20240102_0800.trn'
WITH INIT, COMPRESSION

1.2 备份链与恢复点

时间线：周一00:00 ────────── 周二00:00 ────────── 周三08:00
         │                    │                    │
    完整备份              差异备份              日志备份
    (Full)               (Diff)                (Log)
    
场景：周三08:15 数据库崩溃，恢复到 08:00
恢复顺序：Full → Diff → Log

-- 恢复备份链示例
-- 1. 恢复完整备份
RESTORE DATABASE YourDB 
FROM DISK = 'G:\Backup\Full\YourDB_Full_周一.bak'
WITH NORECOVERY

-- 2. 恢复差异备份
RESTORE DATABASE YourDB 
FROM DISK = 'G:\Backup\Diff\YourDB_Diff_周二.bak'
WITH NORECOVERY

-- 3. 恢复日志备份
RESTORE LOG YourDB 
FROM DISK = 'G:\Backup\Log\YourDB_Log_周三_0800.trn'
WITH RECOVERY

1.3 COPY_ONLY 备份（不破坏备份链）

-- 特殊场景：在不影响现有备份链的情况下做额外备份
-- 例如：升级前、重大变更前的快照备份

BACKUP DATABASE YourDB 
TO DISK = 'G:\Backup\PreUpgrade\YourDB_PreUpgrade.bak'
WITH COPY_ONLY, COMPRESSION

-- 日志备份也支持 COPY_ONLY
BACKUP LOG YourDB 
TO DISK = 'G:\Backup\PreChange\YourDB_Log_PreChange.trn'
WITH COPY_ONLY

二、备份策略设计（RPO/RTO）

2.1 RPO/RTO 定义

术语定义示例
RPO可容忍的最大数据丢失量1小时 → 最多丢失1小时数据
RTO可容忍的最大恢复时间4小时 → 必须在4小时内恢复

2.2 不同业务场景的备份策略

业务类型RPORTO备份策略恢复方案
金融交易<5分钟<30分钟日志备份5分钟Always On 可用性组
电商订单<15分钟<1小时日志备份15分钟日志传送 + 完整备份
ERP系统<4小时<4小时日志备份1小时完整+差异+日志
报表系统<24小时<8小时完整备份每天完整备份
开发测试可丢失<24小时完整备份每周完整备份

2.3 完整备份策略模板

-- 创建存储过程：动态生成备份命令
CREATE PROC dbo.sp_GenerateBackupCommands
AS
BEGIN
    DECLARE @dbname VARCHAR(100)
    DECLARE @fullPath VARCHAR(500)
    DECLARE @diffPath VARCHAR(500)
    DECLARE @logPath VARCHAR(500)
    DECLARE @date VARCHAR(10) = CONVERT(VARCHAR, GETDATE(), 112)
    DECLARE @time VARCHAR(8) = CONVERT(VARCHAR, GETDATE(), 108)
    SET @time = REPLACE(@time, ':', '')
    
    DECLARE db_cursor CURSOR FOR
        SELECT name FROM sys.databases 
        WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
          AND state = 0
          AND source_database_id IS NULL  -- 不是数据库快照
          AND is_in_standby = 0
    
    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @dbname
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 完整备份（周日）
        SET @fullPath = 'G:\Backup\Full\' + @dbname + '_' + @date + '.bak'
        PRINT 'BACKUP DATABASE [' + @dbname + '] TO DISK = ''' + @fullPath + ''' WITH INIT, COMPRESSION'
        
        -- 差异备份（周一至周六）
        SET @diffPath = 'G:\Backup\Diff\' + @dbname + '_Diff_' + @date + '.bak'
        PRINT 'BACKUP DATABASE [' + @dbname + '] TO DISK = ''' + @diffPath + ''' WITH DIFFERENTIAL, INIT, COMPRESSION'
        
        -- 日志备份（每小时）
        SET @logPath = 'G:\Backup\Log\' + @dbname + '_Log_' + @date + '_' + @time + '.trn'
        PRINT 'BACKUP LOG [' + @dbname + '] TO DISK = ''' + @logPath + ''' WITH INIT, COMPRESSION'
        
        FETCH NEXT FROM db_cursor INTO @dbname
    END
    
    CLOSE db_cursor
    DEALLOCATE db_cursor
END

三、恢复实战（核心技能）

3.1 时间点恢复（Point-in-Time Recovery）

-- 场景：2024-01-02 09:35:27 误删了重要数据，恢复到 09:35:00

-- 1. 恢复完整备份
RESTORE DATABASE YourDB 
FROM DISK = 'G:\Backup\Full\YourDB_20240101.bak'
WITH NORECOVERY, REPLACE

-- 2. 恢复差异备份
RESTORE DATABASE YourDB 
FROM DISK = 'G:\Backup\Diff\YourDB_Diff_20240102.bak'
WITH NORECOVERY

-- 3. 恢复日志备份到指定时间点
RESTORE LOG YourDB 
FROM DISK = 'G:\Backup\Log\YourDB_Log_20240102_0900.trn'
WITH STOPAT = '2024-01-02 09:35:00', RECOVERY

-- 4. 如果需要恢复多个日志文件
RESTORE LOG YourDB FROM DISK = 'G:\Backup\Log\YourDB_Log_20240102_0915.trn'
WITH STOPAT = '2024-01-02 09:35:00', RECOVERY

3.2 页级恢复（Page Restore）

-- 场景：检测到特定页损坏（如页 1234）
-- 1. 查看损坏页
DBCC CHECKDB(YourDB) WITH NO_INFOMSGS

-- 2. 页级恢复（需在完整恢复模式下）
RESTORE DATABASE YourDB
PAGE = '1:1234'  -- 文件ID:页号
FROM DISK = 'G:\Backup\Full\YourDB_Full.bak'
WITH NORECOVERY

-- 3. 恢复后续日志
RESTORE LOG YourDB FROM DISK = 'G:\Backup\Log\YourDB_Log.trn'
WITH RECOVERY

3.3 文件/文件组恢复

-- 场景：某个数据文件损坏，只恢复该文件

-- 1. 脱机损坏的文件
ALTER DATABASE YourDB MODIFY FILE (NAME = YourDB_Data2, OFFLINE)

-- 2. 恢复文件
RESTORE DATABASE YourDB
FILE = 'YourDB_Data2'
FROM DISK = 'G:\Backup\Full\YourDB_Full.bak'
WITH NORECOVERY

-- 3. 恢复差异备份
RESTORE DATABASE YourDB
FILE = 'YourDB_Data2'
FROM DISK = 'G:\Backup\Diff\YourDB_Diff.bak'
WITH NORECOVERY

-- 4. 恢复日志
RESTORE LOG YourDB FROM DISK = 'G:\Backup\Log\YourDB_Log.trn'
WITH RECOVERY

-- 5. 联机文件
ALTER DATABASE YourDB MODIFY FILE (NAME = YourDB_Data2, ONLINE)

3.4 数据库快照（快速恢复）

-- 创建数据库快照（用于快速恢复，不能替代备份）
CREATE DATABASE YourDB_Snapshot_20240101 ON
(
    NAME = YourDB_Data,
    FILENAME = 'D:\Snapshots\YourDB_Data_20240101.ss'
)
AS SNAPSHOT OF YourDB

-- 从快照恢复（秒级）
USE master
RESTORE DATABASE YourDB FROM DATABASE_SNAPSHOT = 'YourDB_Snapshot_20240101'

-- 删除快照
DROP DATABASE YourDB_Snapshot_20240101

四、备份优化与压缩

4.1 备份压缩

-- 查看压缩配置
EXEC sp_configure 'backup compression default'

-- 启用默认压缩（推荐）
EXEC sp_configure 'backup compression default', 1
RECONFIGURE

-- 压缩效果对比
-- 未压缩：500GB 备份 → 2小时
-- 压缩后：150GB 备份 → 30分钟

4.2 备份校验与完整性检查

-- 备份时验证完整性
BACKUP DATABASE YourDB 
TO DISK = 'G:\Backup\YourDB.bak'
WITH CHECKSUM, COMPRESSION

-- 恢复前验证备份
RESTORE VERIFYONLY 
FROM DISK = 'G:\Backup\YourDB.bak'
WITH CHECKSUM

-- 查看备份文件内容
RESTORE HEADERONLY FROM DISK = 'G:\Backup\YourDB.bak'
RESTORE FILELISTONLY FROM DISK = 'G:\Backup\YourDB.bak'

4.3 备份到多个文件（提升性能）

-- 将备份分散到多个文件（并行写入）
BACKUP DATABASE YourDB 
TO 
    DISK = 'G:\Backup\YourDB_1.bak',
    DISK = 'G:\Backup\YourDB_2.bak',
    DISK = 'G:\Backup\YourDB_3.bak'
WITH COMPRESSION, STATS = 10

-- 恢复时同样指定多个文件
RESTORE DATABASE YourDB 
FROM 
    DISK = 'G:\Backup\YourDB_1.bak',
    DISK = 'G:\Backup\YourDB_2.bak',
    DISK = 'G:\Backup\YourDB_3.bak'
WITH REPLACE, STATS = 10

五、自动化备份（SQL Agent）

5.1 创建备份作业（T-SQL）

-- 创建 SQL Agent 作业
USE msdb
GO

-- 完整备份作业
EXEC dbo.sp_add_job
    @job_name = N'Daily_Backup_Full',
    @enabled = 1

EXEC dbo.sp_add_jobstep
    @job_name = N'Daily_Backup_Full',
    @step_name = N'Full Backup',
    @command = N'
        BACKUP DATABASE YourDB 
        TO DISK = ''G:\Backup\Full\YourDB_Full_'' + CONVERT(VARCHAR, GETDATE(), 112) + ''.bak''
        WITH INIT, COMPRESSION, CHECKSUM
    '

EXEC dbo.sp_add_schedule
    @schedule_name = N'Sunday_1AM',
    @freq_type = 8,  -- 每周
    @freq_interval = 1,  -- 周日
    @active_start_time = 100  -- 01:00

EXEC dbo.sp_attach_schedule
    @job_name = N'Daily_Backup_Full',
    @schedule_name = N'Sunday_1AM'

EXEC dbo.sp_add_jobserver @job_name = N'Daily_Backup_Full'

5.2 维护计划（图形化替代）

-- 使用系统存储过程创建维护计划
EXEC sp_add_maintenance_plan N'BackupPlan'
EXEC sp_add_maintenance_plan_db N'BackupPlan', N'YourDB'
EXEC sp_add_maintenance_plan_job N'BackupPlan'

六、灾难恢复场景

6.1 系统数据库恢复

-- master 数据库恢复（使用命令行）
sqlservr.exe -f -c -m
-- 然后在查询窗口执行
RESTORE DATABASE master FROM DISK = 'G:\Backup\master.bak'

-- msdb 数据库恢复
RESTORE DATABASE msdb FROM DISK = 'G:\Backup\msdb.bak'

-- model 数据库恢复
RESTORE DATABASE model FROM DISK = 'G:\Backup\model.bak'

6.2 异地备份

-- 使用网络路径（需 SQL Server 服务账户有权限）
BACKUP DATABASE YourDB 
TO DISK = '\\BackupServer\BackupShare\YourDB.bak'
WITH COMPRESSION

-- 备份到 Azure Blob Storage（SQL Server 2012+）
CREATE CREDENTIAL [https://youraccount.blob.core.windows.net/backups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2022-11-02&ss=...'

BACKUP DATABASE YourDB 
TO URL = 'https://youraccount.blob.core.windows.net/backups/YourDB.bak'
WITH COMPRESSION, CREDENTIAL = 'https://youraccount.blob.core.windows.net/backups'

6.3 恢复演练模板

-- 每月执行一次恢复演练，验证备份可用性

-- 1. 恢复到一个测试服务器
RESTORE DATABASE YourDB_RestoreTest 
FROM DISK = 'G:\Backup\Full\YourDB_Full.bak'
WITH 
    MOVE 'YourDB_Data' TO 'D:\Test\YourDB_Data.mdf',
    MOVE 'YourDB_Log' TO 'E:\Test\YourDB_Log.ldf',
    REPLACE, STATS = 10

-- 2. 恢复差异备份
RESTORE DATABASE YourDB_RestoreTest 
FROM DISK = 'G:\Backup\Diff\YourDB_Diff.bak'
WITH NORECOVERY

-- 3. 恢复日志备份
RESTORE LOG YourDB_RestoreTest 
FROM DISK = 'G:\Backup\Log\YourDB_Log.trn'
WITH RECOVERY

-- 4. 验证数据完整性
DBCC CHECKDB(YourDB_RestoreTest)

-- 5. 测试业务查询
SELECT COUNT(*) FROM YourDB_RestoreTest.dbo.Orders
SELECT TOP 10 * FROM YourDB_RestoreTest.dbo.Customers

-- 6. 清理测试数据库
DROP DATABASE YourDB_RestoreTest

七、常见问题与解决方案

7.1 日志文件无限增长

-- 原因：数据库在 FULL 恢复模式下，但从未做日志备份

-- 查看日志使用情况
DBCC SQLPERF(LOGSPACE)

-- 解决方案1：做日志备份
BACKUP LOG YourDB TO DISK = 'G:\Backup\Log\YourDB_Log.trn'

-- 解决方案2：切换到 SIMPLE 模式（如不需要时间点恢复）
ALTER DATABASE YourDB SET RECOVERY SIMPLE
-- 收缩日志
DBCC SHRINKFILE (YourDB_Log, 1024)  -- 收缩到 1GB
-- 切回 FULL 并做完整备份
ALTER DATABASE YourDB SET RECOVERY FULL
BACKUP DATABASE YourDB TO DISK = 'G:\Backup\YourDB_Full.bak'

7.2 备份失败诊断

-- 查看备份历史
SELECT TOP 10
    database_name,
    backup_start_date,
    backup_finish_date,
    type,
    physical_device_name,
    backup_size/1024/1024 AS SizeMB,
    [COMPRESSED_BACKUP_SIZE]/1024/1024 AS CompressedMB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
ORDER BY backup_start_date DESC

-- 查看备份错误
SELECT * FROM msdb.dbo.sysalerts
WHERE enabled = 1

-- 测试备份目录写入权限
EXEC xp_cmdshell 'echo Test > G:\Backup\test.txt'

八、备份恢复检查清单

日常检查

  • 检查所有数据库的最后备份时间
  • 检查备份文件大小和磁盘空间
  • 检查备份文件校验和（定期执行 RESTORE VERIFYONLY）
  • 检查日志空间使用率（< 50%）

每周检查

  • 测试恢复最近3天的备份到测试环境
  • 检查备份链是否完整
  • 清理过期备份文件（保留策略）

每月检查

  • 执行完整的恢复演练
  • 验证 RTO/RPO 是否达标
  • 检查异地备份可用性
  • 更新灾难恢复文档

备份保留策略

备份类型保留时间存储位置
每日完整备份2周本地磁盘
每周完整备份3个月本地磁盘
每月完整备份1年异地存储
年度完整备份7年（合规）归档存储
差异备份1周本地磁盘
日志备份3天本地磁盘

九、核心总结

知识点核心要点
完整备份基础备份，每周至少1次
差异备份只备份变更，每天1次
日志备份实现时间点恢复，频率取决于 RPO
备份链完整 → 差异 → 日志，缺一不可
时间点恢复STOPAT 参数，精确到秒
备份压缩减少空间和时间，推荐启用
恢复演练每月一次，验证备份可用性
RPO/RTO业务驱动备份策略设计

一句话记住本期内容：

备份是 DBA 的第一职责——没有备份就没有 SLA；只有定期演练的备份才是真正的备份。

动手练习

-- 场景：某电商系统，每天产生 50GB 日志，要求 RPO=15分钟，RTO=2小时

-- 1. 设计备份策略（完整、差异、日志的频率）

-- 2. 写出恢复步骤：2024-01-15 10:23:47 误删订单表，恢复到 10:23:00

-- 3. 如何验证备份文件可以恢复？
点击查看参考答案

1. 备份策略：

  • 完整备份：每周日 01:00
  • 差异备份：每天 01:00（周一至周六）
  • 日志备份：每 15 分钟

2. 恢复步骤：

RESTORE DATABASE YourDB FROM DISK = 'G:\Backup\Full\YourDB_20240114.bak' WITH NORECOVERY
RESTORE DATABASE YourDB FROM DISK = 'G:\Backup\Diff\YourDB_Diff_20240115.bak' WITH NORECOVERY
RESTORE LOG YourDB FROM DISK = 'G:\Backup\Log\YourDB_Log_20240115_1000.trn' WITH NORECOVERY
RESTORE LOG YourDB FROM DISK = 'G:\Backup\Log\YourDB_Log_20240115_1015.trn' WITH STOPAT = '2024-01-15 10:23:00', RECOVERY

3. 验证方法：

RESTORE VERIFYONLY FROM DISK = 'G:\Backup\YourDB.bak' WITH CHECKSUM
-- 或每月恢复演练

下一期预告

高可用性——Always On 可用性组与故障转移集群

  • 日志传送（Log Shipping）配置与监控
  • 数据库镜像（已过时，快速了解）
  • Always On 可用性组：架构与最佳实践
  • 故障转移集群实例（FCI）
  • 高可用方案选型指南

📌 本文适用于 SQL Server 2016-2022。生产环境备份策略必须经过 RPO/RTO 评估和恢复演练验证。

管理系列持续更新中，点击关注不错过第四期。

avatar
文章
阅读
粉丝