Início / dba / Perguntas / 244787
Accepted
James Jenkins
Asked: 2019-08-08 09:15:05 +0800 CST

Existe um limite para quantos backups podem ser anexados a um único arquivo?

  • 772

Uma pergunta recente me fez olhar para o MS Documents e me perguntar.

Existe um limite para quantos backups podem ser anexados a um único arquivo?

Por padrão, o SQL Server usa NOINITpara anexar novos backups ao arquivo de backup antigo.

{ NOINIT | INIT } Controla se a operação de backup anexa ou substitui os conjuntos de backup existentes na mídia de backup. O padrão é anexar ao conjunto de backup mais recente na mídia (NOINIT). Fonte

Os documentos dizem claramente que a falta de espaço livre em disco fará com que um backup anexado falhe.

Se um arquivo de disco for preenchido enquanto uma operação de backup estiver anexando um backup ao conjunto de mídia, a operação de backup falhará. O tamanho máximo de um arquivo de backup é determinado pelo espaço livre em disco disponível no dispositivo de disco; portanto, o tamanho apropriado para um dispositivo de disco de backup depende do tamanho de seus backups. Fonte

A resposta em SQL Recover from .bak file with NOINIT indica que o Positionfrom RESTORE HEADERONLYindica o backup individual no arquivo. Que é um campo "pequeno", que deve ter no máximo 32.767

Na maioria das vezes, ao pesquisar no Google, você encontra pessoas que estão anexando acidentalmente seus backups e não conseguem entender por que os backups são tão grandes.

Não encontro referências claras sobre quantos backups podem ser anexados, assumindo espaço em disco suficiente. O limite é 32.767 ou algo totalmente diferente?

sql-server backup

3 respostas

  1. Best Answer

    TL:DR; É possível colocar mais de 32.000 backups em um único arquivo. Se isso é bom ou se você pode recuperar de um backup neste arquivo, não é abordado aqui.

    Comecei a fazer backups de tlog ontem à noite, em um banco de dados existente (231682) sem atividade. Eu usei um loop while e um contador para que eu pudesse obter um total em execução. 

    DECLARE @counter int
SET @counter = 1
While 1=1
Begin 
BACKUP LOG [231682] TO  
DISK = N'G:\SQLBackups\Test_Tlog.trn' WITH NOFORMAT, NOINIT,  
NAME = N'231682-Log Database Backup', SKIP, NOREWIND, NOUNLOAD
SET @counter = @counter + 1
print @counter
End
    • 16 horas depois, a contagem está em 5.967 com um tamanho de arquivo de 356 MB. Isso pode demorar um pouco. No início estava completando 24 backups de log por segundo.
    • 1 dia 16 horas: a contagem é 8.401 com um tamanho de arquivo de 700 MB, sp_whoisactivemostra informações de espera (2029ms)BACKUPTHREAD
    • 4 dias 18 horas: a contagem é 12.834 com um tamanho de arquivo de 1,6 GB sp_whoisactivemostra informações de espera (52113 ms) O tempo de BACKUPTHREAD entre os backups diminuiu para cerca de 70 segundos por backup de log.
    • 5 dias e algumas horas; um evento externo (reinicialização do servidor) fez com que os backups do tlog parassem. a contagem de backups concluídos é de 13.717, o tamanho do arquivo é de 1,8 GB, o tempo entre os backups é de cerca de 80 segundos. Validado com restore headeronly from disk='G:\SQLBackups\Test_Tlog.trn'Não tentou restaurar o banco de dados, pois seria muito doloroso. Defina o contador para iniciar SET @counter = 13717e reiniciar a adição de backups ao mesmo arquivo com o mesmo código. Os backups são retomados e estão demorando cerca de 80 segundos
    • 1 semana; segunda reinicialização devido a problemas externos. A contagem é de 15.186 arquivos com tamanho de 2,3 GB cada backup está levando cerca de 90 segundos.
    • Semana e meia; A contagem é de 17.919, o tamanho do arquivo é de 3,2 GB, cada backup está levando cerca de 2 minutos e 25 segundos.
    • 2 semanas; A contagem é de 19.645, o tamanho do arquivo é de 3,8 GB, cada backup está levando cerca de 2 minutos e 45 segundos.
    • 3 semanas; contagem é 22.919 tamanho do arquivo é 5,2 GB cada backup está demorando cerca de 3 minutos e 30 segundos (17 por hora) como estou executando os infinitos backups t-log em um trabalho agora, adicionei isso ao código RAISERROR(N'Count equals :%d', 16, 1, @counter ) WITH LOG;para que a contagem em execução seja exibida em o log de erros do SQL Obrigado @Erik Darling
    • 4 semanas; contagem é 25.587 tamanho do arquivo é 6,6 GB cada backup está levando cerca de 3 minutos e 45 segundos (16 por hora)
    • 5 semanas; contagem é 28.242 tamanho do arquivo é 8 GB cada backup está levando cerca de 4 minutos e 35 segundos (13,5 por hora)
    • 6 semanas; contagem é 30.037 tamanho do arquivo é 9,1 GB cada backup está levando cerca de 5 minutos (12 por hora)
    • ~ 7 semanas: falta de espaço no disco de backup dedicado. Os backups começam a falhar quando há espaço insuficiente, mas o trabalho continua em execução e tentando. Pare todo o resto, ganhe um pouco mais de espaço excluindo alguns arquivos. A contagem nos logs do SQL está errada, pois conta as tentativas com falha e as bem-sucedidas.
      • Parar trabalho
      • O tamanho do arquivo é de 10,4 GB cada backup leva cerca de 5 minutos (12 por hora), o tempo é o mesmo em caso de sucesso ou falha.
      • Verifique o backup com a restore headeronly from disk='G:\SQLBackups\Test_Tlog.trn'contagem é 32.021
      • O cabeçalho indica que todos os backups bem-sucedidos estão bem.
      • A maioria dos backups mostra um valor de 75.776 para BackupSizee um valor de ~4.000 para CompressedBackupSixe. O tamanho compactado varia em cada backup.
      • Eu não tentei uma restauração com o arquivo t-log. Suspeito que haverá problemas e gostaria de tentar antes de tornar tudo tão feio quanto agora.
      • Exclua o arquivo t-log, reinicie os backups normais.

    Estou compactando backups por padrão nesta instância.

    Semana 3 Nota: O tamanho do arquivo e o tempo de backup estão crescendo desproporcionalmente ao número de backups. Observando os cabeçalhos tlog, vemos que o backup na posição 2 tem um tamanho de 75766 bytes e um tempo de início ao término de um segundo ou menos. O backup na posição 22919 também tem um tamanho de 75766 bytes e um tempo de início para término de um segundo ou menos. A sobrecarga de anexar os backups ao mesmo arquivo parece estar causando a lentidão. O crescimento anormal provavelmente está relacionado a tarefas de manutenção semanais que executei na instância.

    Backups externos, parece que minha solução de backup externo (IBM Spectrum) não está fazendo backup do arquivo trn. Eu suspeito que isso é porque o arquivo está sendo constantemente editado.

    Edite , algum tempo depois. Eu estava pensando em fazer outro experimento para testar a recuperação em cerca de 30.000 backups. Para evitar os problemas de tentar restaurar vários t-logs, analisei o uso de backups diferenciais. Criei um banco de dados vazio, fiz um backup completo e depois fiz 10 backups diferenciais. Então fiz 10 backups de t-logs e usando RESTORE HEADERONLY FROM DISKeu comparei o tamanho, os backups diferenciais são significativamente maiores que os t-logs, não tenho espaço suficiente para realizar um bom teste.

    Backups diferenciais 2-10 ( o primeiro é sempre um pouco maior )

    • BackupSize = 1126400
    • Tamanho de backup compactado = 62271

    Backups T-Logs 2-10 ( o primeiro é sempre um pouco maior )

    • BackupSize = 75776
    • CompressedBackupSize = 3800 ( média, varia )

    Os backups diferenciais são cerca de 16 vezes maiores, na melhor das hipóteses eu só poderia obter cerca de 2.000 deles, não estou fazendo mais testes no momento.

    • 7

  2. Não deve haver outro limite além do tamanho máximo de arquivo possível. O arquivo de backup é gravado no formato de fita da Microsoft e novos cabeçalhos são simplesmente anexados ao arquivo.

    Bloco de descrição de volume MTF

    • 3

  3. Existe um limite para quantos backups podem ser anexados a um único arquivo?

    3285.

    Na verdade, isso até onde eu cheguei. O arquivo de backup chegou a 10 GB e cada backup estava demorando 10 segundos, então eu não estava disposto a esperar mais.

    Usando:

    use master
go
create database bt
go
backup database bt to disk='c:\temp\bt.bak' with noinit
go 10000

restore headeronly from disk='c:\temp\bt.bak' 

restore database bt from disk='c:\temp\bt.bak' with file = 3285, replace
    • 2

relate perguntas