主页 / dba / 问题 / 342410
Accepted
Don
Asked: 2024-09-17 07:29:47 +0800 CST

SQL .bak 文件是否被压缩而没有明确说明要压缩？

  • 772

我使用以下脚本制作生产数据库的 .bak 文件并将其保存到测试服务器，如果需要，可以在该服务器上恢复。该脚本未说明要压缩文件。运行 SQL Server 2019 Enterprise。

BACKUP DATABASE [PINK] 
    TO DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH NOFORMAT, INIT,  NAME = N'PINK', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO

DECLARE @backupSetId AS int

SELECT @backupSetId = position 
FROM msdb..backupset 
WHERE database_name = N'PINK' 
  AND backup_set_id = (SELECT MAX(backup_set_id) 
                       FROM msdb..backupset 
                       WHERE database_name = N'PINK')

IF @backupSetId IS NULL
BEGIN
    RAISERROR(N'Verify failed. Backup information for database ''PINK'' not found.', 16, 1) 
END

RESTORE VERIFYONLY 
    FROM DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

.bak测试服务器上的内存大小根据您在 Explorer 中查看的位置而定，为 162GB 或 154GB ：

在此处输入图片描述

.bak为了节省空间，我尝试通过添加COMPRESSION脚本来压缩：

BACKUP DATABASE [PINK] 
    TO DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH NOFORMAT, INIT, NAME = N'PINK', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM
GO

DECLARE @backupSetId AS int

SELECT @backupSetId = position 
FROM msdb..backupset 
WHERE database_name = N'PINK' 
  AND backup_set_id = (SELECT MAX(backup_set_id) 
                       FROM msdb..backupset 
                       WHERE database_name = N'PINK')

IF @backupSetId IS NULL
BEGIN
    RAISERROR(N'Verify failed. Backup information for database ''PINK'' not found.', 16, 1) 
END

RESTORE VERIFYONLY 
    FROM DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

执行包含的命令后COMMPRESSION，它在测试服务器上的大小相同。我检查了.ndf和.mdf文件，它们总共是 770GB 或 732GB，具体取决于您查看的位置。此外，它还包含大约 23GB 的索引信息。

在此处输入图片描述

.bak尽管我没有声明要这么做，但这是否已经被压缩了？

sql-server-2019

3 个回答

  1. 压缩可以作为默认行为。

    您可以运行此查询来检查它：

    SELECT value
FROM   sys.configurations
WHERE  name = 'backup compression default'

    0表示备份压缩关闭，1表示备份压缩打开。

    有关更多信息，请参阅https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/view-or-configure-the-backup-compression-default-server-configuration-option

    • 6

  2. SSMS > 右键单击​​服务器实例 > 属性 >数据库设置> 压缩备份。

    您可以检查默认状态或者更改它。

    在此处输入图片描述

    在此处输入图片描述

    • 3
  3. Best Answer

    其他答案解释了可能的原因，即服务器配置已启用压缩备份，因此无论您是否要求，您都会获得它。 如果这是原因，您将看到对应msdb.dbo.backupset于备份的行将compression_algorithm填充（'MS_XPRESS'），并且compressed_backup_size可能与不同。backup_size如果备份未压缩，则将compression_algorithm是NULL和backup_size和compressed_backup_size将是相同的。

    SELECT backup_size, compressed_backup_size, compression_algorithm
  FROM msdb.dbo.backupset
  WHERE { your where clause };

    您还可以检查Compressed以下列RESTORE HEADERONLY：

    RESTORE HEADERONLY 
  FROM DISK = '\\TestServer\Transfer\PINK.bak'
  WITH FILE = @backupSetId;

    如果该设置被禁用，只需启用它：

    EXEC sys.sp_configure N'backup compression default', 1;
GO
RECONFIGURE WITH OVERRIDE;

    我不知道你为什么不打开这个功能，或者你为什么不希望总是进行压缩，除非你试图通过使用更密集/更有效的第三方选项来压缩未压缩的备份，从而最大限度地提高最终压缩率。在所需空间、I/O 和带宽方面，你肯定没有取得任何进展，而且我不知道 7-Zip 和其他软件现在的效率会有多高。

    但还有另一种可能性：也许您的数据压缩效果不佳，或者已经压缩（使用页面、行或列存储）。在这种情况下，备份压缩可能效果很小甚至没有效果；如果是这样，您不太可能看到备份WITH COMPRESSION与不备份之间的太大差异。

    关于将多个备份附加到单个备份的几点注意事项.bak：

    • 千万不要这么做。这种“效率”可以追溯到物理磁带，只会造成混乱（甚至数据损坏，因为人们只是运行恢复而没有意识到默认情况下他们得到的是第一个/最旧的文件）。更好的做法是始终使用WITH INIT并通过嵌入时间戳使输出文件唯一且自文档化；这可以通过各种免费工具自动完成，例如DBAToolsBackup-DbaDatabase（使用-TimeStampFormat）和Ola Hallengren 的 SQL Server Backup（使用@FileName = N'...{Year}{Month}{Day}_{Hour}{Minute}{Second}...'）。
    • 一个更相关的混淆示例：如果您压缩了备份中的第一个文件，则无论您是否明确表示- 并同意，后续文件都会Compressed = 1显示。我期待出现错误（如果您尝试另一种方式，您会得到错误：不压缩然后压缩）。从我所看到的一切来看，那些后续备份都是压缩的。这实际上是您当前情况的第三个潜在解释：您的第一个备份是压缩的，因此所有其余的都继承了该选项。RESTORE HEADERONLYWITH COMPRESSIONmsdbPINK.BAK
    • 2

相关问题