É possível estimar ganho de compressão de backup?

Olá,

É possível determinar o tamanho de uma backup comprimido com WITH COMPRESSION?

Observação: Recomendo a leitura do  post sobre backup compression aqui do blog além da documentação oficial, para melhor aproveitamento do assunto deste post.

 

Essa é a resposta do BOL que você pode conferir aqui:

For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes

Quando este questionamento foi feito a mim, o banco de dados  em questão nunca teve backup, não tinha valor algum no MSDB.DBO.BACKUPSET pra sequer dar uma ideia de qual seria o tamanho do .bak e além disso espaço em disco, nesta situação em específico, também era um problema. E ganho de compressão é aquela coisa: cada caso é um caso, não tem números mágicos pra multiplicar ou dividir e chegar nesta resposta. Quando qualquer backup com compressão já foi realizado, é possível obter o que é chamado de “Compression Rate”, dividindo o tamanho da base pelo tamanho da base comprimida. O Compression rate ajuda pra ter uma boa ideia de quanto será comprimido.  A fórmula básica é:

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

Mas bem, se não dá pra estimar qual será o tamanho do backup comprimido se nenhum backup foi realizado, daria certo criar um  um  backup “fake” pra obter informações na backupset? Entenda-se por backup “fake” no contexto deste post aquele backup que é realizado normalmente, logado no msdb mas nada é gravado e persistido em disco. Trata-se do backup enviado para o NUL device, assunto já comentando e que você pode conferir  neste post.

IT’S DEMO TIME!

Tudo foi testado em uma instância 2008 R2, embora possa ser testado em qualquer versão 2008+ que possua a feature de compressão de backup.

Tenho uma base chamada DB_BESTIARIO que possui 484.37 MB.

Trata-se de um repositório e que no momento do teste estava sem atividade alguma, o que é perfeito para este exemplo diga-se de passagem. Esse banco de dados não tem nenhum backup realizado (Veja o resultset vazio).

SELECT
		backup_set_id,
		database_name,
		CAST((backup_size/1024.)/1024. AS DEC(12,2)) AS [Backup Normal MB] ,
		CAST((compressed_backup_size/1024.)/1024. AS DEC (12,2)) AS [Backup Compress.MB] ,
		CAST(backup_size/compressed_backup_size AS DEC(12,2)) as [Ganho (x)]
FROM msdb.dbo.backupset
WHERE database_name = 'DB_BESTIARIO'

Agora, vamos tirar um backup FULL normal, sem compressão e conferir o que foi logado na backupset:

BACKUP DATABASE DB_BESTIARIO TO DISK = 'D:\STAGE\DB_BESTIARIO.bak'

Nada demais. O tamanho do campo compressed backup size é exatamente o mesmo do backup_Size se não for utilizado compressão. Apenas com esta informação, não consigo estimar o tamanho do backup comprimido.

Então, vamos tirar um backup apontando pro NUL:

 BACKUP DATABASE DB_BESTIARIO TO DISK = 'NUL'

Note que o tamanho do backup feito pra NUL é o mesmo do backup realizado fisicamente em disco. Estes tamanhos conferem com a realidade do primeiro backup tirado?

.. 151.636 KB equivale à 148MB. Agora, vamos tirar um backup COM compressão e conferir qual tamanho o SQL Server loga no backupset:

 BACKUP DATABASE DB_BESTIARIO TO DISK = 'NUL' WITH COMPRESSION

Aqui está a observação principal do teste. Fazendo um backup “fake”, dá pra estimar que o backup comprimido é três vezes menor que o tamanho do backup sem compressão. Mas nem por isso existe a certeza de que o ganho do comprimido x original será sempre 3x menor pra todo backup daquela base, embora isso seja favorável para praticamente todos os cenários. Através deste teste, é possível estimar o tamanho do backup sem efetivamente gerar algum arquivo de saída.

Só pra fechar o teste, vamos tirar um backup comprimido para o disco (ou seja, vai gerar arquivo persistido em disco) pra ver se vai dar esses 45.97MB de fato. Só pra validar se não foi viagem confiar no tamanho que o backup pro NUL mostrou.

 BACKUP DATABASE DB_BESTIARIO TO DISK = 'D:\STAGE\DB_BESTIARIO_COMPR.bak' WITH COMPRESSION

 

 

Mais uma prova cabal do backup comprimido em disco:

IMPORTANTE (!)

– O backup é “fake” mas o processo de leitura em disco é real, ou seja, há processamento e leitura durante a criação do backup. A única coisa que muda é o destino do arquivo. Não existe essa do backup ser fictício e não consumir recursos do servidor (no caso, disco, memória e CPU).
– Use com cautela e saiba em quais situações aplicar. Por exemplo, se a base em questão tiver backups diferenciais, se você fizer um backup full desta forma sem COPY_ONLY, haverá uma modificação no DCM (Differential Change Mapping) e aquele backup será necessário se for necessário restaurar aquele diferencial que vem em seguida. No exemplo não usei COPY_ONLY pois se trata de uma base de testes. Se for para backup de log, a coisa é um pouco mais séria: mandar um backup de log quebra cadeia de backup de log, e nunca queira contar com um backup que não existe, é a pior coisa que tem quando o assunto é recovery.

RESUMO

Não é possível afirmar com precisão qual será o tamanho do backup com compressão a não ser que você tire um. Então a resposta para a pergunta “É possível estimar o tamanho de um backup comprimido” continua a mesma do BOL (Não, só tendo algum backup . O que apresento aqui é uma forma alternativa de obter essa informação.  Como Backup to NUL não é salvo em disco. Significa na prática que você pode estimar espaço de um backup daquela base de 2TB sem ter problemas com espaço em disco por causa do arquivo de backup. Foi o que usei para resolver essa questão, especificamente.

Obrigado pela sua leitura, e…I’ll be back!

---

Referências

Backup Compression – https://technet.microsoft.com/en-US/library/bb964719.aspx