Full, Diferencial e T-Log – Salve seus dados

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