Entendendo o CleanupTime nos scripts do SQL Server de Ola Hallengren em relação aos backups FULL e LOG

O @CleanupTimeé sempre especificado para uma tarefa de backup específica. Por exemplo, se você criar uma tarefa de backup completo , uma tarefa de backup diferencial e uma tarefa de backup de log de transações@CleanupTime , sempre estará relacionada à extensão da tarefa.

Vamos dar uma olhada em um exemplo de backup completo .

Backup completo

Se você criar um trabalho de backup completo, normalmente adicionará um ou mais dos seguintes parâmetros:

• @Databases: Quais bancos de dados são submetidos a backup (não é realmente relevante para este exemplo)
• @Directory: O diretório para armazenar os backups
• @BackupType: Completo, Diferencial, TLog
• @CleanupTime: quantas horas de backups devem ser mantidas
• @FileExtensionFull: A extensão do seu backup.

Portanto, você tem um trabalho de backup que criará um backup completo de acordo com o agendamento definido para o trabalho em. Vamos supor o seguinte:

• este trabalho é executado às 20:00 (20:00)
• @FileExtensionFullfoi definido para'BAK'
• @Directoryfoi definido para'F:\SQLBACKUP'
• @CleanupTimefoi definido para 24(horas)

Se olharmos para o MaintenanceSolution.sqlarquivo, você encontrará a descrição do parâmetro:

SET @CleanupTime         = NULL         
-- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.

Bem, isso não está ajudando muito. O mesmo que na documentação oficial no site. Vamos cavar mais. Se você examinar o script, eventualmente encontrará uma seção parecida com esta:

_{O script foi empacotado para aumentar a legibilidade}

SE @BackupSoftware FOR NULO
    COMEÇAR
        SET @CurrentCommandType02 = 'xp_delete_file'
        SET @CurrentCommand02 =
            'DECLARE @ReturnCode int
            EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0,
                N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''', -- primeiro parâmetro
                ''' + @CurrentFileExtension + ''', --segundo parâmetro
                ''' + CONVERT(nvarchar(19),@CurrentCleanupDate,126) + ''' -- terceiro parâmetro
        IF @ReturnCode 0 RAISERROR(''Erro ao excluir arquivos.'', 16, 1)'
    FIM

Então Ola está basicamente usando o xp_delete_file functionSQL Server embutido para excluir um arquivo em um determinado momento de acordo com:

• @CurrentDirectoryPath
• @CurrentFileExtension
• @CurrentCleanupDate

Mas espere o que seria, por exemplo, o @CurrentCleanupDateser? Se voltarmos um pouco no script, você encontrará uma seção parecida com esta:

INSERT INTO @CurrentCleanupDates (CleanupDate, Mirror)
    SELECT DATEADD(hh,-(@CleanupTime),GETDATE()), 0

Ah, então @CurrentCleanupDateé uma adição de data que é calculada a partir da @CleanupTimee da hora atual GETDATE(). Legal.
_{(... e podemos ter encontrado um erro de digitação no código, porque as seções para banco de dados normal e espelho contêm Mirroro código.)}

Para que serve então a seção relevante @CurrentFileExtension? Vamos pesquisar um pouco novamente. E encontramos:

SELECT @CurrentFileExtension = CASE
    QUANDO @CurrentBackupType = 'FULL' THEN @FileExtensionFull
    QUANDO @CurrentBackupType = 'DIFF' ENTÃO @FileExtensionDiff
    QUANDO @CurrentBackupType = 'LOG' THEN @FileExtensionLog
    FIM

Então você tem isso.

Resumo

Se os parâmetros da tarefa de backup completo@FileExtensionFull='BAK' estiverem definidos como e você tiver definido um @CleanupTime=24, o procedimento excluirá todos os arquivos de backup completo com pelo menos um dia (24 horas).

O @CurrentCommand02que é executado é basicamente:

xp_delete_file 0, 'F:\SQLBACKUP', 'BAK', '2018-08-20 20:00:00.045'

Portanto, não toca em nenhum outro arquivo de backup. (A menos que você tenha definido 'BAK'como extensão de todos os tipos de backup, nesse caso você perde).

Eu votei na resposta do @hot2use, pois aborda essa questão em detalhes, mas queria compartilhar uma maneira fácil de testar essas coisas.

Pode ajudá-lo (como me ajudou) a entender completamente como o script funciona se você:

1. Instale o script de backup (e outras dependências) em uma instância de teste - testei no meu computador local.
2. Agora, altere o script e pesquise/substitua hhpor minute. As únicas referências que posso encontrar hhsão onde o script está lidando com o tempo de limpeza. Isso permite que você execute rapidamente backups de vários tipos (FULL, DIFF, LOG) veja os efeitos da execução, pois a retenção é em minutos e não em horas.

Execute um backup FULL, DIFF e LOG de um banco de dados de teste e observe os arquivos criados nas pastas individuais. Isto é o que eu usei (observe o CleanupTimede 1 minuto devido a alterar o script de horas para minutos):

exec [dbo].[DatabaseBackup]
@Databases = 'test',
@Directory = 'C:\OlaBackupTest',
@BackupType = 'full',
@Verify = 'N',
@CleanupTime = 1,
@CleanupMode = 'AFTER_BACKUP'

exec [dbo].[DatabaseBackup]
@Databases = 'test',
@Directory = 'C:\OlaBackupTest',
@BackupType = 'diff',
@Verify = 'N',
@CleanupTime = 1,
@CleanupMode = 'AFTER_BACKUP'

exec [dbo].[DatabaseBackup]
@Databases = 'test',
@Directory = 'C:\OlaBackupTest',
@BackupType = 'log',
@Verify = 'N',
@CleanupTime = 1,
@CleanupMode = 'AFTER_BACKUP'

Meus testes revelaram as seguintes observações:

CHEIO

Cada execução de um FULLbackup criava um novo FULLbackup e excluía todos FULLos arquivos de backup com mais de 1 minuto. Nenhum DIFFou LOGarquivos de backup foram afetados.

DIFF

Cada execução de um DIFFbackup criava um novo DIFFbackup e excluía todos DIFFos arquivos de backup com mais de 1 minuto. Nenhum FULLou LOGarquivos de backup foram afetados.

REGISTRO

Cada execução de um LOGbackup criava um novo LOGbackup. Os backups contínuos LOG(sem intervenção FULLou DIFFbackups) simplesmente continuaram a se acumular na LOGpasta de backup, independentemente do tempo de limpeza. Se um backup foi feito, a PRÓXIMA execução do backup excluiu todos os backups anteriores ao último FULLou também anteriores a 1 minuto .DIFFLOGLOGFULLDIFF

Nenhum arquivo FULLde DIFFbackup foi afetado durante a execução de LOGbackups.

Eu recomendaria manter mais de 1 semana de FULLbackups no caso de você precisar voltar no tempo para restaurar. Assumindo 2 semanas de FULLbackups, você precisaria CleanupTimede 336 horas.

Durante o teste do material de 1 minuto, você verá que:

• A execução de FULLbackups nunca exclui DIFFou LOGbackups
• A execução de DIFFbackups nunca exclui FULLou LOGbackups
• A execução de LOGbackups nunca exclui FULLou DIFFbackups

Expandindo as outras duas respostas, porque acabei de encontrar isso:

Pode valer a pena notar (para quem pode google isso). para permitir um pouco mais do que o tempo exato em que você deseja manter seu backup.

Se o op quiser manter o backup de 1 semana, as 24 horas por dia nem sempre manterão o arquivo de 1 semana. Motivo: Os bancos de dados estão crescendo. Seu novo backup pode demorar um pouco mais.

Se o seu primeiro arquivo de backup foi criado 2020-03-15 03:00:05.000 E o próximo backup demora um pouco mais e o arquivo é criado 2020-03-22 30:00:0 6 .000 - isso é mais do que o especificado 168 horas (um segundo, lembre-se)

Então, para estar seguro, faça 24x7+1!