印刻数据库

外观

SQLServer 备份实施与验证 ​

备份实施前准备 ​

环境检查 ​

在实施SQLServer备份前，需要进行以下环境检查：

  1. 磁盘空间检查：

    sql
    -- 检查备份目标磁盘可用空间
EXEC xp_fixeddrives;

-- 检查数据库大小，估算备份所需空间
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    SUM(size * 8 / 1024) AS SizeMB
FROM sys.master_files
GROUP BY database_id;

  2. 权限检查：

    • 确保执行备份的用户具有BACKUP DATABASE和BACKUP LOG权限
    • 对于系统数据库，需要sysadmin或db_owner角色

  3. SQL Server代理状态检查：

    sql
    -- 检查SQL Server代理服务状态
SELECT 
    status_desc,
    start_date,
    is_auto_restart
FROM sys.dm_server_services
WHERE servicename LIKE '%SQL Server Agent%';

  4. 恢复模式检查：

    sql
    -- 检查数据库恢复模式
SELECT 
    name AS DatabaseName,
    recovery_model_desc
FROM sys.databases;

备份设备配置 ​

本地磁盘备份设备 ​

sql
-- 创建本地磁盘备份设备
EXEC sp_addumpdevice 
    @devtype = N'disk', 
    @logicalname = N'AdventureWorks2022_Backup', 
    @physicalname = N'D:\Backup\AdventureWorks2022.bak';

网络共享备份设备 ​

sql
-- 创建网络共享备份设备
EXEC sp_addumpdevice 
    @devtype = N'disk', 
    @logicalname = N'AdventureWorks2022_Network_Backup', 
    @physicalname = N'\\BackupServer\SQLBackup\AdventureWorks2022.bak';

Azure Blob Storage备份设备 ​

sql
-- 创建Azure Blob Storage备份凭证
CREATE CREDENTIAL [https://mybackups.blob.core.windows.net/sqlbackups] 
WITH IDENTITY = 'Shared Access Signature',
SECRET = 'sv=2021-06-08&ss=bfqt&srt=sco&sp=rwdlacupyx&se=2025-12-31T23:59:59Z&st=2025-01-01T00:00:00Z&spr=https&sig=YourSignatureHere';

-- 使用Azure Blob Storage进行备份
BACKUP DATABASE [AdventureWorks2022] 
TO URL = N'https://mybackups.blob.core.windows.net/sqlbackups/AdventureWorks2022_Full_20251227.bak' 
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2022-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

备份实施步骤 ​

完整备份实施 ​

使用SSMS进行完整备份 ​

  1. 打开SQL Server Management Studio，连接到SQL Server实例
  2. 展开"数据库"节点，右键单击要备份的数据库
  3. 选择"任务" > "备份..."
  4. 在"备份数据库"对话框中：
    • 选择"备份类型"为"完整"
    • 选择"备份到"为"磁盘"
    • 点击"添加"按钮，指定备份文件路径
    • 配置"备份集"选项，包括名称和描述
    • 配置"介质选项"，选择是否覆盖现有备份集
    • 配置"压缩"选项，选择是否启用备份压缩
  5. 点击"确定"执行备份

使用T-SQL进行完整备份 ​

sql
-- 基本完整备份
BACKUP DATABASE [AdventureWorks2022] 
TO DISK = N'D:\Backup\AdventureWorks2022_Full_20251227.bak' 
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2022-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

-- 启用压缩的完整备份
BACKUP DATABASE [AdventureWorks2022] 
TO DISK = N'D:\Backup\AdventureWorks2022_Full_Compressed_20251227.bak' 
WITH COMPRESSION, NOFORMAT, NOINIT, NAME = N'AdventureWorks2022-Full Compressed Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

-- 多设备并行备份
BACKUP DATABASE [AdventureWorks2022] 
TO DISK = N'D:\Backup\AdventureWorks2022_Full_1.bak',
    DISK = N'E:\Backup\AdventureWorks2022_Full_2.bak',
    DISK = N'F:\Backup\AdventureWorks2022_Full_3.bak' 
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2022-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

差异备份实施 ​

使用T-SQL进行差异备份 ​

sql
-- 基本差异备份
BACKUP DATABASE [AdventureWorks2022] 
TO DISK = N'D:\Backup\AdventureWorks2022_Diff_20251227_1200.bak' 
WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'AdventureWorks2022-Differential Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

-- 启用压缩的差异备份
BACKUP DATABASE [AdventureWorks2022] 
TO DISK = N'D:\Backup\AdventureWorks2022_Diff_Compressed_20251227_1200.bak' 
WITH DIFFERENTIAL, COMPRESSION, NOFORMAT, NOINIT, NAME = N'AdventureWorks2022-Differential Compressed Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

事务日志备份实施 ​

使用T-SQL进行事务日志备份 ​

sql
-- 基本事务日志备份
BACKUP LOG [AdventureWorks2022] 
TO DISK = N'D:\Backup\AdventureWorks2022_Log_20251227_1215.trn' 
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2022-Transaction Log Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

-- 启用压缩的事务日志备份
BACKUP LOG [AdventureWorks2022] 
TO DISK = N'D:\Backup\AdventureWorks2022_Log_Compressed_20251227_1215.trn' 
WITH COMPRESSION, NOFORMAT, NOINIT, NAME = N'AdventureWorks2022-Compressed Transaction Log Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

-- 截断事务日志（仅在简单恢复模式下）
BACKUP LOG [AdventureWorks2022] WITH TRUNCATE_ONLY;

系统数据库备份 ​

备份系统数据库 ​

sql
-- 备份master数据库
BACKUP DATABASE [master] 
TO DISK = N'D:\Backup\master_Full_20251227.bak' 
WITH NOFORMAT, NOINIT, NAME = N'master-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

-- 备份msdb数据库
BACKUP DATABASE [msdb] 
TO DISK = N'D:\Backup\msdb_Full_20251227.bak' 
WITH NOFORMAT, NOINIT, NAME = N'msdb-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

-- 备份model数据库
BACKUP DATABASE [model] 
TO DISK = N'D:\Backup\model_Full_20251227.bak' 
WITH NOFORMAT, NOINIT, NAME = N'model-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

备份自动化实施 ​

使用SQL Server代理作业自动化备份 ​

创建完整备份作业 ​

  1. 打开SQL Server Management Studio，连接到SQL Server实例
  2. 展开"SQL Server代理"节点
  3. 右键单击"作业"，选择"新建作业..."
  4. 在"新建作业"对话框中：
    • 配置"常规"选项，输入作业名称和描述
    • 切换到"步骤"页，点击"新建..."
    • 配置步骤：
      • 步骤名称："完整备份"
      • 类型："Transact-SQL脚本(T-SQL)"
      • 数据库："master"
      • 命令：输入完整备份T-SQL语句
    • 点击"确定"保存步骤
    • 切换到"调度"页，点击"新建..."
    • 配置调度：
      • 名称："每日完整备份"
      • 频率：每天
      • 时间：凌晨0:00
    • 点击"确定"保存调度
    • 切换到"通知"页，配置作业完成时的通知方式
    • 点击"确定"创建作业

示例作业脚本 ​

sql
-- 创建完整备份作业
USE [msdb]
GO

EXEC dbo.sp_add_job 
    @job_name = N'Backup - All Databases Full', 
    @enabled = 1, 
    @description = N'Full backup for all user databases';

EXEC dbo.sp_add_jobstep 
    @job_name = N'Backup - All Databases Full', 
    @step_name = N'Execute Full Backup', 
    @subsystem = N'TSQL', 
    @command = N'EXECUTE dbo.DatabaseBackup 
    @Databases = ''USER_DATABASES'', 
    @Directory = N''D:\Backup'', 
    @BackupType = ''FULL'', 
    @Verify = ''Y'', 
    @Compress = ''Y'', 
    @CheckSum = ''Y'', 
    @CleanupTime = 72,
    @LogToTable = ''Y'';', 
    @retry_attempts = 3, 
    @retry_interval = 5;

EXEC dbo.sp_add_jobschedule 
    @job_name = N'Backup - All Databases Full', 
    @name = N'Daily Full Backup', 
    @freq_type = 4, 
    @freq_interval = 1, 
    @active_start_time = 000000;

EXEC dbo.sp_add_jobserver 
    @job_name = N'Backup - All Databases Full', 
    @server_name = N'(local)';
GO

使用维护计划自动化备份 ​

  1. 打开SQL Server Management Studio，连接到SQL Server实例
  2. 展开"管理"节点
  3. 右键单击"维护计划"，选择"新建维护计划..."
  4. 在"新建维护计划"对话框中，输入计划名称，选择"每项任务单独计划"或"单个计划用于整个维护计划"
  5. 从工具箱中拖放"备份数据库"任务到设计器
  6. 双击"备份数据库"任务，配置备份选项：
    • 选择备份类型（完整、差异、事务日志）
    • 选择要备份的数据库
    • 配置备份目标
    • 配置压缩选项
    • 配置验证选项
    • 配置备份文件保留期
  7. 配置计划调度
  8. 保存维护计划

备份验证方法 ​

备份集验证 ​

使用RESTORE VERIFYONLY ​

sql
-- 验证完整备份
RESTORE VERIFYONLY 
FROM DISK = N'D:\Backup\AdventureWorks2022_Full_20251227.bak' 
WITH CHECKSUM;

-- 验证事务日志备份
RESTORE VERIFYONLY 
FROM DISK = N'D:\Backup\AdventureWorks2022_Log_20251227_1215.trn' 
WITH CHECKSUM;

使用备份校验和 ​

sql
-- 备份时启用校验和
BACKUP DATABASE [AdventureWorks2022] 
TO DISK = N'D:\Backup\AdventureWorks2022_Full_CheckSum_20251227.bak' 
WITH CHECKSUM, NOFORMAT, NOINIT, NAME = N'AdventureWorks2022-Full Database Backup with CheckSum', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

-- 还原时验证校验和
RESTORE DATABASE [AdventureWorks2022_Test] 
FROM DISK = N'D:\Backup\AdventureWorks2022_Full_CheckSum_20251227.bak' 
WITH CHECKSUM, NORECOVERY, 
MOVE N'AdventureWorks2022' TO N'D:\Data\AdventureWorks2022_Test.mdf', 
MOVE N'AdventureWorks2022_log' TO N'E:\Log\AdventureWorks2022_Test_log.ldf';

数据完整性验证 ​

使用DBCC CHECKDB验证恢复的数据 ​

sql
-- 恢复数据库后验证数据完整性
DBCC CHECKDB ([AdventureWorks2022_Test]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

定期恢复测试 ​

完整恢复测试流程 ​

  1. 准备测试环境：

    • 确保有足够的磁盘空间
    • 确保测试环境与生产环境隔离

  2. 执行恢复操作：

    • 恢复完整备份
    • 恢复最新差异备份
    • 恢复所有事务日志备份

  3. 验证恢复结果：

    • 检查数据库是否能正常启动
    • 执行DBCC CHECKDB验证数据完整性
    • 检查关键表的数据是否完整
    • 运行应用程序测试脚本验证业务功能

  4. 记录恢复时间：

    • 记录开始恢复时间
    • 记录恢复完成时间
    • 计算恢复总时间
    • 评估是否满足RTO要求

  5. 清理测试环境：

    • 删除测试数据库
    • 清理测试备份文件

示例恢复测试脚本 ​

sql
-- 完整恢复测试脚本
-- 1. 恢复完整备份
RESTORE DATABASE [AdventureWorks2022_Test] 
FROM DISK = N'D:\Backup\AdventureWorks2022_Full_20251227.bak' 
WITH NORECOVERY, 
MOVE N'AdventureWorks2022' TO N'D:\Data\AdventureWorks2022_Test.mdf', 
MOVE N'AdventureWorks2022_log' TO N'E:\Log\AdventureWorks2022_Test_log.ldf';

-- 2. 恢复差异备份
RESTORE DATABASE [AdventureWorks2022_Test] 
FROM DISK = N'D:\Backup\AdventureWorks2022_Diff_20251227_1200.bak' 
WITH NORECOVERY;

-- 3. 恢复事务日志备份
RESTORE LOG [AdventureWorks2022_Test] 
FROM DISK = N'D:\Backup\AdventureWorks2022_Log_20251227_1215.trn' 
WITH NORECOVERY;

RESTORE LOG [AdventureWorks2022_Test] 
FROM DISK = N'D:\Backup\AdventureWorks2022_Log_20251227_1230.trn' 
WITH NORECOVERY;

RESTORE LOG [AdventureWorks2022_Test] 
FROM DISK = N'D:\Backup\AdventureWorks2022_Log_20251227_1245.trn' 
WITH RECOVERY;

-- 4. 验证恢复结果
DBCC CHECKDB ([AdventureWorks2022_Test]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- 5. 检查关键表数据
SELECT TOP 10 * FROM [AdventureWorks2022_Test].[Sales].[SalesOrderHeader];
SELECT COUNT(*) FROM [AdventureWorks2022_Test].[Sales].[SalesOrderDetail];

-- 6. 删除测试数据库
DROP DATABASE [AdventureWorks2022_Test];

备份监控 ​

监控备份作业状态 ​

查询作业历史记录 ​

sql
-- 查询最近7天的备份作业历史
SELECT 
    j.name AS JobName,
    h.step_name AS StepName,
    h.run_date,
    h.run_time,
    CASE h.run_status
        WHEN 0 THEN '失败'
        WHEN 1 THEN '成功'
        WHEN 2 THEN '重试'
        WHEN 3 THEN '取消'
        WHEN 4 THEN '正在进行'
    END AS RunStatus,
    h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE j.name LIKE '%Backup%'
    AND h.run_date >= CONVERT(INT, CONVERT(VARCHAR(8), DATEADD(DAY, -7, GETDATE()), 112))
ORDER BY h.run_date DESC, h.run_time DESC;

配置作业通知 ​

  1. 打开SQL Server Management Studio，连接到SQL Server实例
  2. 展开"SQL Server代理"节点
  3. 展开"作业"节点
  4. 右键单击要配置通知的作业，选择"属性"
  5. 在"作业属性"对话框中：
    • 切换到"通知"页
    • 配置通知方式：
      • 电子邮件：选择操作员和通知条件
      • 寻呼：选择操作员和通知条件
      • net send：输入接收者名称和通知条件
      • 写入Windows事件日志：选择通知条件
    • 点击"确定"保存配置

监控备份存储使用情况 ​

查询备份文件大小 ​

sql
-- 查询备份文件大小
SELECT 
    database_name AS DatabaseName,
    backup_type = CASE type
        WHEN 'D' THEN '完整备份'
        WHEN 'I' THEN '差异备份'
        WHEN 'L' THEN '事务日志备份'
        WHEN 'F' THEN '文件或文件组备份'
        WHEN 'G' THEN '差异文件备份'
        WHEN 'P' THEN '部分备份'
        WHEN 'Q' THEN '差异部分备份'
    END,
    backup_start_date AS StartTime,
    backup_finish_date AS FinishTime,
    datediff(second, backup_start_date, backup_finish_date) AS DurationSeconds,
    backup_size / 1024 / 1024 AS BackupSizeMB,
    compressed_backup_size / 1024 / 1024 AS CompressedSizeMB,
    100 - (compressed_backup_size * 100 / backup_size) AS CompressionRatioPercent
FROM msdb.dbo.backupset
WHERE backup_start_date >= DATEADD(DAY, -7, GETDATE())
ORDER BY backup_start_date DESC;

监控备份存储容量 ​

powershell
# PowerShell脚本监控备份存储容量
$backupPath = "D:\Backup"
$warningThreshold = 80  # 警告阈值：80%
$criticalThreshold = 90  # 临界阈值：90%

# 获取备份存储使用情况
$drive = Get-WmiObject -Class Win32_LogicalDisk -Filter "DeviceID='$($backupPath.Substring(0,2))'"
$freeSpaceGB = [math]::Round($drive.FreeSpace / 1GB, 2)
$totalSpaceGB = [math]::Round($drive.Size / 1GB, 2)
$usedSpaceGB = $totalSpaceGB - $freeSpaceGB
$usedPercentage = [math]::Round(($usedSpaceGB / $totalSpaceGB) * 100, 2)

# 输出监控结果
Write-Host "备份存储监控报告"
Write-Host "存储路径: $backupPath"
Write-Host "总容量: $totalSpaceGB GB"
Write-Host "已用容量: $usedSpaceGB GB ($usedPercentage%)"
Write-Host "可用容量: $freeSpaceGB GB"

# 检查是否超过阈值
if ($usedPercentage -ge $criticalThreshold) {
    Write-Host "[CRITICAL] 备份存储容量已超过临界阈值 $criticalThreshold%"
    # 发送临界告警
} elseif ($usedPercentage -ge $warningThreshold) {
    Write-Host "[WARNING] 备份存储容量已超过警告阈值 $warningThreshold%"
    # 发送警告告警
} else {
    Write-Host "[OK] 备份存储容量正常"
}

备份常见问题处理 ​

备份失败常见原因及解决方法 ​

常见原因解决方法
磁盘空间不足清理磁盘空间，或扩展备份存储
权限不足检查执行备份的用户权限，确保具有BACKUP DATABASE和BACKUP LOG权限
备份设备不可用检查备份设备路径是否正确，网络共享是否可用
数据库正在使用确保没有其他进程锁定数据库，或使用WITH COPY_ONLY选项
事务日志损坏执行DBCC CHECKDB检查数据库完整性，必要时修复数据库
SQL Server代理服务未运行启动SQL Server代理服务

事务日志备份失败处理 ​

sql
-- 事务日志备份失败时的处理步骤

-- 1. 检查数据库状态
SELECT 
    name AS DatabaseName,
    state_desc,
    recovery_model_desc
FROM sys.databases
WHERE name = 'AdventureWorks2022';

-- 2. 检查事务日志状态
DBCC SQLPERF(LOGSPACE);

-- 3. 尝试执行CHECKDB检查
DBCC CHECKDB ('AdventureWorks2022') WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- 4. 如果无法修复，可以考虑以下应急措施
-- 注意：此操作会导致数据丢失，仅在紧急情况下使用
ALTER DATABASE [AdventureWorks2022] SET RECOVERY SIMPLE;
CHECKPOINT;
ALTER DATABASE [AdventureWorks2022] SET RECOVERY FULL;
BACKUP DATABASE [AdventureWorks2022] TO DISK = N'D:\Backup\AdventureWorks2022_Emergency_Full.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2022-Emergency Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

最佳实践 ​

  1. 使用备份压缩：减少备份时间和存储空间
  2. 启用备份校验和：确保备份的完整性
  3. 使用COPY_ONLY备份：避免中断正常的备份链
  4. 定期清理旧备份：避免备份存储溢出
  5. 监控备份作业：及时发现和解决备份失败问题
  6. 定期测试恢复：确保备份可以正常恢复
  7. 分离系统数据库和用户数据库备份：便于管理和恢复
  8. 使用自动化工具：减少手动操作，提高备份可靠性
  9. 记录备份历史：便于审计和问题排查
  10. 遵循3-2-1备份原则：确保备份的安全性和可用性

常见问题（FAQ） ​

Q1: 如何查看备份历史记录？ ​

A: 可以使用以下方法查看备份历史记录：

  1. 使用SQL Server Management Studio：
    • 展开"数据库"节点
    • 右键单击数据库，选择"属性"
    • 切换到"选项"页，点击"查看备份历史记录..."
  2. 使用T-SQL查询：
    sql
    SELECT * FROM msdb.dbo.backupset WHERE database_name = 'AdventureWorks2022' ORDER BY backup_start_date DESC;

Q2: 备份压缩比一般是多少？ ​

A: 备份压缩比取决于数据的压缩性，一般在2:1到5:1之间：

  • 文本数据压缩比较高，可达5:1
  • 已压缩数据（如图片、视频）压缩比较低，约1.1:1
  • 平均压缩比约为3:1

Q3: 如何提高备份速度？ ​

A: 可以通过以下方法提高备份速度：

  1. 使用更快的存储设备（如SSD）
  2. 分离备份文件和数据文件到不同的存储设备
  3. 启用备份压缩
  4. 使用多个备份设备并行备份
  5. 在非高峰时段执行备份
  6. 增加备份缓冲区大小

Q4: 如何备份只读数据库？ ​

A: 只读数据库可以使用以下方法备份：

  1. 使用常规的完整备份命令
  2. 对于包含只读文件组的数据库，可以使用部分备份
  3. 只读数据库不需要事务日志备份

Q5: 如何备份加密数据库？ ​

A: 备份加密数据库的方法与普通数据库相同，但需要注意：

  1. 确保备份包含数据库加密密钥（DEK）
  2. 如果使用TDE加密，需要备份证书和私钥
  3. 恢复时需要先恢复证书和私钥

Q6: 如何自动化备份验证？ ​

A: 可以通过以下方法自动化备份验证：

  1. 在备份作业中添加验证步骤
  2. 使用PowerShell脚本定期执行RESTORE VERIFYONLY
  3. 使用第三方备份工具，如Ola Hallengren的DatabaseBackup脚本
  4. 配置备份验证失败告警

版本差异 ​

SQLServer版本备份实施差异
2008 R2仅Enterprise版本支持备份压缩
2012引入Columnstore索引备份支持
2014改进了备份压缩算法，提高了压缩速度
2016所有版本都支持备份压缩
2017支持直接备份到Azure Blob Storage
2019引入加速数据库恢复(ADR)，提高了恢复速度
2022支持备份到S3兼容存储，改进了备份性能
2024增强了备份加密功能，支持更高级的加密算法

总结 ​

SQLServer备份实施与验证是确保数据安全的关键环节。通过合理的备份设备配置、自动化备份作业、定期备份验证和恢复测试，可以确保备份的可靠性和可用性。在实施备份时，需要根据数据库的重要性和业务需求选择合适的备份类型和策略，并定期监控备份作业状态和存储使用情况。同时，需要遵循最佳实践，确保备份的安全性、完整性和可恢复性。