1. 为什么备份压缩会失败？先看底层逻辑

当DBA执行BACKUP DATABASE命令时启用压缩功能，SQL Server会调用MS-XPRESS算法（微软专用压缩算法）。失败通常发生在以下场景：

  • 数据库存在大量已压缩对象（如FILESTREAM数据）
  • 硬件资源瓶颈导致压缩超时
  • 使用了不兼容的备份设备类型
  • 参数组合产生冲突（例如同时启用加密和旧版压缩）

典型报错示例：

-- 尝试对已压缩的LOB数据再次压缩时出现的错误
Msg 3004, Level 16, State 1
Cannot perform a differential backup because the base file contains offline files.

2. 参数调优：你需要知道的隐藏开关

2.1 COMPRESSION与NO_COMPRESSION的博弈

通过实例演示不同参数组合的影响：

-- 完整备份启用现代压缩算法（SQL Server 2016+）
BACKUP DATABASE AdventureWorks2022 
TO DISK = 'D:\Backup\AW_Full.bak'
WITH COMPRESSION, STATS = 5;  -- STATS显示进度百分比

-- 对比传统压缩方式（SQL Server 2008+）
BACKUP DATABASE AdventureWorks2022 
TO DISK = 'D:\Backup\AW_Legacy.bak'
WITH COMPRESSION, FORMAT, MEDIANAME = 'LegacyCompress';
/*
FORMAT参数会覆盖现有媒体集
MEDIANAME需与恢复计划中的定义匹配
*/
2.2 MAXTRANSFERSIZE的秘密

调整传输块大小可解决大文件压缩失败问题：

Import-Module SQLPS
$server = New-Object Microsoft.SqlServer.Management.Smo.Server("localhost")

$backup = New-Object Microsoft.SqlServer.Management.Smo.Backup
$backup.Database = "AdventureWorks2022"
$backup.Devices.AddDevice("D:\Backup\AW_Optimized.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$backup.MaxTransferSize = 4194304  # 设置为4MB块大小
$backup.CompressionOption = [Microsoft.SqlServer.Management.Smo.BackupCompressionOptions]::On
$backup.SqlBackup($server)

3. 当标准方法失效时的逃生通道

3.1 使用跟踪标志控制压缩行为
-- 启用跟踪标志3042跳过压缩预处理
DBCC TRACEON(3042, -1);
BACKUP DATABASE AdventureWorks2022 TO DISK='D:\Backup\AW_SkipPreprocess.bak' 
WITH COMPRESSION;
DBCC TRACEOFF(3042, -1);
3.2 分文件备份策略
-- 将数据库拆分为多个文件进行并行压缩
BACKUP DATABASE AdventureWorks2022 
FILEGROUP = 'PRIMARY',
FILEGROUP = 'SECONDARY'
TO DISK='D:\Backup\AW_Primary.bak',
   DISK='D:\Backup\AW_Secondary.bak'
WITH COMPRESSION, BUFFERCOUNT = 16;  -- 增加缓冲区数量

4. 加密证书的正确使用姿势，备份加密与压缩的兼容方案

-- 创建证书并执行加密压缩备份
CREATE CERTIFICATE BackupCert  
   WITH SUBJECT = 'Compressed Backup Encryption';
GO

BACKUP DATABASE AdventureWorks2022 
TO DISK = 'D:\Backup\AW_Encrypted.bak'
WITH COMPRESSION, 
     ENCRYPTION (
       ALGORITHM = AES_256,
       SERVER CERTIFICATE = BackupCert
     ),
     MAXTRANSFERSIZE = 1048576;  -- 加密时建议减小传输块大小

5. 应用场景全景图：什么时候需要调整参数？

场景类型 典型特征 推荐调整策略
OLAP系统 大尺寸维度表 增大BUFFERCOUNT+MAXTRANSFERSIZE
实时日志 高频事务写入 禁用压缩或使用TRACE 3042
云存储备份 网络带宽受限 启用压缩+调整CHECKSUM验证

6. 技术选型对比表：不同方案的取舍之道

方案 压缩率 CPU消耗 恢复速度 适用版本
默认压缩 中等 低 快 2008+
XPRESS算法 高 高 中 2016+
第三方工具 极高 极高 慢 跨版本

7. 避坑指南：老司机总结的血泪经验

  1. 内存分配原则：每1GB压缩缓冲区需要额外2MB内存
  2. 版本兼容性检查清单：
    • 2008R2及更早版本不支持加密压缩
    • Linux版SQL Server需要liblz4库支持
  3. 性能监控黄金指标： 
    SELECT start_time, total_elapsed_time/1000 AS seconds,
       compressed_backup_size/1048576.0 AS compressed_MB
FROM msdb.dbo.backupset
WHERE database_name = 'AdventureWorks2022';

8. 总结：构建稳健的备份压缩体系

通过分析超过真实案例，有效备份压缩配置需遵循：

  1. 渐进式调优：从默认参数开始逐步调整
  2. 环境感知：根据存储类型（SSD/HDD）选择块大小
  3. 验证闭环：每次调整后必须执行恢复测试