Backup, Tech, Virtual PASS BR

Full, Diferencial e T-Log – Salve seus dados

5/5 - (1 vote)

Dando sequência à série de posts sobre backup, iniciada com os posts “BACKUP – Você está fazendo corretamente?” e “Política de backup – Implante esta ideia“, este post será um pouco mais voltado à prática, demonstrando como executar os três tipos de backup mais comuns e que de uma forma básica, podem garantir que seus dados estejam a salvos, caso bem implantados.

Não tenho intenção aqui, de substituir, copiar ou quaisquer outras formas de referências aos meios de documentação oficial do SQL SERVER no que refere-se a Backup. A ideia desta série de posts, é mostrar de forma clara, simples e prática “POR QUE”, “QUANDO” e “COMO” realizar seus backups e implantar política, documentação e etc.

Para saber quais, que tipos, e quando foram gerados os backups de uma determinada instância, pode-se consultar:

SELECT * FROM msdb..backupset

No SQL SERVER, existem diversos tipos de backup, já citados nos posts anteriores, porém os principais tipos para garantir-se que TODOS seus dados poderão ser restaurados são três: “Backup Full”, “Backup Differential” e “Backup de Transaction Log”, abaixo segue explicação rápida, com opções e exemplos de cada um deles.

Abaixo, é criado um banco de dados “DB_BACKUP_EXEMPLO ” para exemplificar e permitir testes na prática dos backups que serão demonstrados em seguida.

CREATE DATABASE [DB_BACKUP_EXEMPLO]

GO

BACKUP FULL

O backup full, ou completo de um banco de dados no SQL Server leva consigo todas as páginas de dados deste banco, além do log das transações ativas durante a execução do backup.Caso seja feito um backup com as opções “default” conforme abaixo, o tamanho do backup full será igual ao tamanho das páginas utilizadas dentro do espaço reservado para este banco, somado ao tamanho do log das transações que estavam ativas durante a execução do backup, que pode durar desde minutos até horas, para garantir a consistência dos dados armazenados, o arquivo de backup full contém esta porção do log de transações.

O exemplo abaixo, gera um backup full com as opções “default”, pois as mesmas não são explicitamente informadas no script que gera o backup

BACKUP DATABASE [DB_BACKUP_EXEMPLO]

TO DISK = N’C:tempBackups_SQLSERVERBKP_BACKUP_EXEMPLO.BAK’

Além das opções default para um backup, é possível definir explicitamente algumas opções, abaixo segue exemplo de algumas:

INIT [INIT / NOINIT] – Esta opção define que você vai ou não sobrescrever o arquivo de backup no diretório em que este está sendo salvo, você pode optar por dar um “append” nos arquivos de backup gerados, e no momento do restore informar qual o arquivo será utilizado para gerar o backup.

COMPRESSION – com esta opção, pode-se gerar o arquivo de backup já compactado, caso a opção de compressão de backup seja explicitamente informada, o SQL Server executa uma verificação do backup com “CHECKSUM” muito recomendada para economiza de espaço para armazenamento do backup

STATS = 10 – Define em porcentagem um valor para que a console do SQL Server exiba o progresso da geração do backup. Caso não seja especificado, o resultado só é exibido no final da execução do backup. Para bases de dados muito grandes, é extremamente aconselhável que se defina esta opção, pois ter-se-á a informação do progresso do backup, podem estimar-se o tempo decorrido e restante para conclusão da operação.

Existem outra dezenas de opções a serem definidas durante a geração de um backup no SQL Server, porém a intenção aqui é descrever de maneira simples e objetiva os meios de geração dos backups mais comuns. Para descrição completa e detalhada dos backups no SQL Server, é possível consultar no site do MSDN.

O script de backup full com as opções supracitadas ficaria da seguinte forma:

BACKUP DATABASE [DB_BACKUP_EXEMPLO]
TO DISK = N’C:tempBackups_SQLSERVERBKP_DB_BACKUP_EXEMPLO_FULL.BAK’
WITH INIT
,COMPRESSION
,STATS = 10

BACKUP DIFFERENTIAL

O backup diferencial gera uma cópia em disco da diferença das páginas de dados alteradas a partir do último BACKUP FULL. Um pré requisito para que seja possível a criação de um backup deste tipo, é que exista um backup full desta base antes de se criar o backup diferencial.

Diferente do backup de log de transações, que veremos a seguir ainda neste post, o backup diferencial é cumulativo, ou seja, cada backup gerado, contém todas as alterações desde o último backup full. Exemplo: Se uma rotina executa um backup diferencial automaticamente de segunda a sexta, o backup da quinta feira, conterá os dados de todos os demais backups de segunda, terça, quarta, quinta, que foram gerados após o último Full.

A sintaxe é idêntica ao backup full e aceitas as mesmas opções, exceto que para informar que o backup que se deseja gerar é do tipo DIFFERENTIAL, deve-se incluir esta opção na lista de opções informadas para o backup full. Um exemplo de backup diferencial do nosso banco DB_BACKUP_EXEMPLO, seria:

BACKUP DATABASE [DB_BACKUP_EXEMPLO]
TO DISK = N’C:tempBackups_SQLSERVERBKP_DB_BACKUP_EXEMPLO_DIFF.BAK’
WITH DIFFERENTIAL
,COMPRESSION
,STATS = 10

BACKUP TRANSACTION LOG

O log de transações é ao meu ver um dos pontos mais complexos e fascinantes dentro do SQL Server, é ele quem garante a integridade e consistência da base em casos de falha, e também permite através de operações de backup / restore que sejam feitas por exemplos recuperações ao exato ponto antes de uma falha do banco, servidor ou instância.

O T-LOG gera uma sequência numérica, crescente e única chamada de LOG CHAIN ou Cadeia de Log, que é utilizada entre outras coisas para garantir uma sequência em que as transações são realizadas e gravadas e assim permitir a restauração das mesmas. Para maiores detalhes sobre este fantástico recurso do SQL Server, indico a leitura das páginas do MSDN que tratam a respeito.

O backup do log de transações depende diretamente do Modelo de Recuperação com que o banco está configurado e é necessário a realização de backup para controlar seu crescimento.

A sintaxe de criação para o backup do log de transações é um pouco diferente, porém tão simples quanto. Segue exemplo:

BACKUP LOG [DB_BACKUP_EXEMPLO]
TO DISK = N’C:tempBackups_SQLSERVERBKP_DB_BACKUP_EXEMPLO_LOG.BAK’
WITH COMPRESSION
,STATS = 5

BACKUP TAIL LOG

O backup denominado como TAIL DO LOG, é exatamente o mesmo tipo de backup do log de transações, porém este deixa a base inacessível, para que não haja quaisquer alterações após a conclusão deste backup, alguns exemplos de utilização para o backup do tail do log são:

  • Para mover o banco entre instâncias, e garantir que nenhuma transação será feita durante a geração do backup de log no destino e restore na origem
  • Em caso de falha do banco, é possível na maioria das vezes alterar o status do banco para o modo EMERGENGY e efetuar o backup do tail do log
  • entre outras…

Segue exemplo da sintaxe de criação de um backup com tail do log:

BACKUP LOG [DB_BACKUP_EXEMPLO]
TO DISK = N’C:tempBackups_SQLSERVERBKP_DB_BACKUP_EXEMPLO_TAIL_LOG.BAK’
WITH NORECOVERY

Estes foram exemplos simples e práticos da criação dos três tipos de backup mais comuns do SQL Server e que sem requerer muita complexidade podem garantir o mínimo de “data loss” para o seu ambiente, caso sigam as recomendações e boas práticas algumas citadas em meus posts anteriores…

Mais uma vez, peço que caso tenha chegado até este ponto no artigo, por favor, deixe seu comentário, sugestão, crítica ou qualquer coisa, para que possa avaliar a qualidades dos conteúdos postados e estar em melhoria contínua…

Abraço….

Edvaldo Castro

@edvaldocastro02

Tagged , , , , , , , , , ,

15 thoughts on “Full, Diferencial e T-Log – Salve seus dados

  1. Opa meu caro.

    no meu caro, ocorreu, para a versão 2016 uma situação estranha.

    Backup full = Domingo ( mode simple )
    Backup diff = todos os dias ( mode simple )

    Segunda alterei o modo para full e comecei a fazer os backups de log, e ele “aceitou” a geração tão somente o Diff ter concluido.

    E que a documentação diz que somente habilita o backup de log depois de um backup full + mode full.

    1. Opa, tudo bem Marco?

      então, a premissa básica para você fazer um backup de log é ter um backup full (independente de quando foi feito. Como você alterou o modo de compatibilidade para full, o seu backup de log tem como referência o backup full que vc já vinha fazendo anteriormente.

      por esse motivo,funcionou no seu caso, se entendi bem seu cenário,

      Abraço e obrigado pela visita.

      Edvaldo Castro

  2. Edvaldo, bom dia!
    Quero agradecer pela excelente conteúdo disponível no seu portal.
    Segue seus passos e parei no backup do log de transação, segue a mensagem sql retorna:

    ALTER DATABASE TESTE SET RECOVERY FULL

    BACKUP LOG TESTE
    TO DISK = ‘C:backuplog.trn’
    GO
    Erro

    Msg 4214, Level 16, State 1, Line 1

    BACKUP LOG cannot be performed because there is no current database backup.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP LOG is terminating abnormally.

  3. Gostei do post. Backup/Restore é um dos assuntos mais fascinantes na área de dados e o artigo está muito bem explicado, sendo uma ótima indicação pra quem ainda confunde as opções existentes.
    Outra coisa que notei é que grande parte das postagens são com esse tema. Isso é bom porque se torna tanto referência de indicação de artigo como de pesquisas futuras.
    Obrigado pela contribuição!
    []’s

    1. Muito obrigado Renato,

      Gosto muito do assunto Alta Disponibilidade e Disaster Recovery, e o assunto backup está intimamente ligado a estes dois…

      Abraços,

      Edvaldo Castro

  4. Valeu pelo artigo Edvaldo…

    Não querendo abusar, mas você pode dar uma clareada na diferença das opções No_truncate e Norecovery para fazer backup do Tail Log?

    Valeu

    1. Fala Fabrício,

      Basicamente a opção NO_TRUNCATE permite você fazer backup da porção ativa do log de transações quando um banco está incessível, porém seu arquivo de log esteja acessível.
      Enquanto a opção NORECOVERY é realizada para deixar o banco inacessível a fim de evitar-se que novas transações sejam realizadas neste banco.

      Obrigado pelo feedback,

      Abraço

  5. Grande Edvaldo, to gostando de ver essa serie e o melhor de tudo, você empolgado para produzir conteúdo. Estou lendo todos eles e estou gostando bastante, pois são assuntos simples, mas que são rotineiros.

    Uma dica já que você esta nessa serie de posts é falar sobre os modelos de recovery, encontro muitas pessoas vindo tirar essas duvidas comigo e acho importante de falar sobre.

    Abraços,
    Marcos Freccia

    1. Valeu Freccia,

      Esta série de posts ainda vai longe… existem muitos tópicos a serem cobertos. Obrigado pelo feedback e pela dica, devo escrever mais alguns posts descrevendo os processos de backup e restores mais comuns e em seguida falar um pouco das particularidades que envolvem cada um deles…

      Abraço,

      Edvaldo Castro

Leave a Reply

Your email address will not be published. Required fields are marked *