1. 首页
  2. SQL Server
  3. SQL Server 数据库备份压缩

SQL Server 数据库备份压缩

2019/01/17 16:19:36 发表 2019/01/19 15:10:28 更新 原创 雨辰 1732 3 0
[TOCM]

一、前言

在 SQL Server 数据库日常维护中，进行数据库备份是一项十分必要的工作。随着数据量不断增长，数据库备份的文件也会不断增长，而数据库备份文件经常需要，转储到其他存储设备的。如果不转储到其他设备，也就失去了，保护数据的意义。

对数据库备份进行压缩，不仅能减少磁盘空间占用，还能减少文件在传输时占用的宽带资源。可谓是一举两得。

二、备份压缩介绍

介绍 SQL Server 备份压缩，包括优势、限制、性能影响、压缩率、为备份文件分配空间。另外对版本的要求，需要 SQL Server 2008 版本，或以上版本的数据库才能使用。

1. 优势

相比未压缩的备份文件来说，经过压缩的备份文件会更小，所以压缩后的文件所需要的设备 I/O（读写）会更少一些，因此可大大提高备份速度。另外，文件变小后，文件的转储所占用的带宽资源也会更小，传输速度更快。

2. 限制

压缩的备份具有以下限制条件：

  • 压缩的备份和未压缩的备份不能共存于一个介质集中。
  • 早期版本的 SQL Server 无法读取压缩的备份。
  • NTbackup 无法共享包含压缩的 SQL Server 备份的磁带。
3. 性能影响

默认情况下，压缩会显著增加 CPU 的使用，并且压缩进程所消耗的额外 CPU 可能会对并发操作产生不利影响。因此，你可能需要在会话中创建低优先级的压缩备份，其 CPU 使用率受资源调控器限制。

4. 压缩率

压缩备份的压缩率取决于所压缩的数据。有多种因素会影响所获得的压缩率。主要因素包括：

  • 数据类型。
    字符数据的压缩率要高于其他类型的数据。
  • 页面上各行间数据的一致性。
    通常，如果某页包含多个行，而其中的某个字段包含相同的值，则该值可获得较大的压缩。相反，对于包含随机数据或者每页只有一个很大的行的数据库，压缩备份的大小几乎与未压缩的备份相同。
  • 数据是否加密。
    与未加密数据相比，同样的加密数据的压缩率要小得多。如果使用透明数据加密来加密整个数据库，则压缩备份不会将数据库大小减小很多，甚至根本不会减小。
  • 数据库是否压缩。
    如果压缩数据库，则压缩备份不会将大小减小很多，甚至根本不会减小。
5. 为备份文件分配空间

对于压缩的备份，最终备份文件的大小取决于数据可压缩程度，这在备份操作完成之前是未知的。因此，默认情况下，在使用压缩备份数据库时，数据库引擎将预先分配算法用于备份文件。此算法为备份文件预先分配数据库大小的预定义的百分比。如果在备份操作过程中需要更多空间，则数据库引擎会增大该文件。如果最终大小小于分配的空间，则在备份操作结束时，数据库引擎会将该文件收缩到备份的实际的最终大小。

若要允许备份文件仅在需要时增大以便达到其最终大小，则使用跟踪标志 3042。 跟踪标志 3042 导致备份操作绕过默认的备份压缩预先分配算法。 如果您需要仅分配压缩的备份所需的实际大小以便节约空间，则此跟踪标志将很有用。 但是，使用此跟踪标志可能会导致轻微的性能损失（在备份操作期间损失可能会增加）。

三、使用备份压缩

在数据库安装时，默认情况下关闭了备份压缩。你也可更改默认配置，开启备份压缩，这需要修改 SQL Server 的实例配置。通常情况下，不推荐修改，还是推荐即用即开启，减少性能和资源损失。以下将要介绍的每一种，使用备份压缩的方式，都是通过显示启用备份压缩，或显示禁用备份压缩的，都是临时的，不对默认配置做更改。

1. Transact-SQL

在 Transact-SQL 的 backup 语句中使用WITH NO_COMPRESSION或WITH COMPRESSION进行数据库备份。NO_COMPRESSION表示显式禁用备份压缩；COMPRESSION表示显式启用备份压缩。如下例子：

  1. declare @filename varchar(255)
  3. set @filename = 'D:\Microsoft SQL Server\Backup\BlogDB_' + 
  4.                 convert(varchar(50),getdate(),112) + '_' +
  5.                 cast(DATEDIFF(SS, '1970-1-1 00:00:00', GETUTCDATE()) as varchar) + '.bak' 
  7. BACKUP DATABASE [Blog] TO 
  8. DISK = @filename 
  9. WITH NOFORMAT, NOINIT, 
  10. NAME = N'BlogDB-完整数据库备份', 
  11. SKIP, NOREWIND, NOUNLOAD, 
  12. STATS = 1, COMPRESSION
2. SQL Server Management Studio

使用 SSMS（SQL Server Management Studio）“管理”>“维护计划”>“新建维护计划”或“维护计划向导”创建数据库备份，其中选项就有备份压缩。具体可看这篇文章《SQL Server 2016 数据库自动备份之维护计划》

四、参考文献

微软文档 - 备份压缩 (SQL Server)

微软文档 - 配置备份压缩 (SQL Server)

微软文档 - BACKUP (Transact-SQL)

（完）