Início / dba / Perguntas / 342410
Accepted
Don
Asked: 2024-09-17 07:29:47 +0800 CST

O arquivo SQL .bak é compactado sem que seja explicitamente declarado que deve ser compactado?

  • 772

Eu uso o script a seguir para fazer um .bak de um banco de dados de produção e salvá-lo em um servidor de teste onde ele pode ser restaurado se necessário. O script não declara para compactar o arquivo. Executando o 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

Ele .bakvai para o servidor de teste e, dependendo de onde você olha no Explorer, ele tem 162 GB ou 154 GB:

insira a descrição da imagem aqui

Para economizar espaço, tentei compactar .bakadicionando COMPRESSIONao script:

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

Após executar com o COMMPRESSIONcomando incluído, ele caiu no mesmo tamanho no servidor de teste. Eu verifiquei os arquivos .ndfe .mdfe eles totalizam 770 GB ou 732 GB, dependendo de onde você olha. Além disso, ele inclui cerca de 23 GB de informações de índice.

insira a descrição da imagem aqui

Isso .bakjá está compactado, mesmo que eu não tenha dito isso?

sql-server-2019

3 respostas

  1. A compactação pode estar ativa como comportamento padrão.

    Você pode executar esta consulta para verificar isso:

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

    0 significa que a compactação de backup está desativada, 1 significa que a compactação de backup está ativada.

    Para obter mais informações, consulte https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/view-or-configure-the-backup-compression-default-server-configuration-option

    • 6

  2. SSMS > Clique com o botão direito do mouse em Instância do servidor > Propriedades > Configurações do banco de dados > Compactar backup .

    Você pode verificar o status padrão ou alterá-lo.

    insira a descrição da imagem aqui

    insira a descrição da imagem aqui

    • 3
  3. Best Answer

    As outras respostas explicam a causa provável, que a configuração do servidor para compactar backups está habilitada, então você está obtendo isso, quer tenha pedido ou não. Se essa for a causa, você verá que a linha em msdb.dbo.backupsetque corresponde ao backup terá sido compression_algorithmpreenchida ( 'MS_XPRESS'), e compressed_backup_sizeprovavelmente será diferente de backup_size. Se o backup não foi compactado, então compression_algorithmserá NULLe backup_sizee compressed_backup_sizeserão os mesmos.

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

    Você também pode conferir a Compressedcoluna em RESTORE HEADERONLY:

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

    Se a configuração estiver desabilitada, basta habilitá-la:

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

    Não sei por que você não teria isso ativado, ou por que você não gostaria que a compactação acontecesse sempre, a menos que você esteja tentando maximizar a compactação final usando opções de terceiros muito mais intensivas/eficazes para compactar um backup descompactado. Você certamente não está mais à frente em termos de espaço necessário, E/S e largura de banda, e eu não sei o quanto mais eficazes o 7-Zip e outros podem ser hoje, de qualquer forma.

    Mas há outra possibilidade: talvez seus dados simplesmente não sejam bem compactados, ou já estejam compactados (usando page, row ou columnstore). Nesse caso, a compactação de backup pode ter pouco ou até mesmo nenhum efeito; se for assim, é improvável que você veja muita diferença entre backups WITH COMPRESSIONe sem.

    Algumas notas sobre anexar vários backups a um único .bak:

    • Simplesmente não faça isso. Essa "eficiência" remonta às fitas físicas e não causa nada além de confusão (e até mesmo corrupção de dados, pois as pessoas apenas executam uma restauração sem perceber que obtêm o primeiro/mais antigo arquivo por padrão). Uma prática muito melhor é sempre usar WITH INITe tornar os arquivos de saída únicos e autodocumentados incorporando carimbos de data/hora; isso pode ser automatizado com várias ferramentas gratuitas, como DBAToolsBackup-DbaDatabase (usando -TimeStampFormat) e SQL Server Backup de Ola Hallengren (usando @FileName = N'...{Year}{Month}{Day}_{Hour}{Minute}{Second}...').
    • Um exemplo mais relevante de confusão: se você comprimiu o primeiro arquivo no backup, os arquivos subsequentes dirão Compressed = 1in, RESTORE HEADERONLYindependentemente de você dizer explicitamente WITH COMPRESSION- e msdbagrees. Eu estava esperando um erro (que você obtém se tentar o contrário: sem compressão e depois compressão). De tudo que posso ver, esses backups posteriores são compactados. Esta é, na verdade, uma terceira explicação potencial para seu cenário atual: seu primeiro backup in PINK.BAKfoi compactado, então todos os outros estão herdando essa opção.
    • 2

relate perguntas