Três Modelos de Recuperação no SQL Server: Simple, Full e Bulk-logged

Existem TRÊS diferentes modelos de recuperação do SQL Server, você deve selecionar o modelo de recuperação do SQL Server para gerenciar os arquivos de log e se preparar para a recuperação do SQL em caso de desastre. Este documento é para falar sobre três modelos de recuperação do SQL Server: simple, full e bulk-logged. Entre todos, você deve fazer backup do banco de dados do SQL Server.

Este documento recomenda um software confiável de backup e recuperação de banco de dados do SQL Server: EaseUS Todo Backup Advanced Server.

Informação básica sobre arquivos de log

O banco de dados do SQL Server contém pelo menos um arquivo de dados mdf e um arquivo de log ldf. O arquivo Mdf contém todos os objetos e dados do banco de dados, como tabela, procedimento armazenado e informações do usuário. O arquivo de log Ldf contém todos os logs no banco de dados. No entanto, você nunca apenas depende dos arquivos de log para recuperar seu banco de dados, por exemplo, para recuperar o banco de dados para algum ponto de tempo por meio do arquivo de log ldf e do backup completo anterior.

O que o arquivo de log faz?

• Sendo a posição de armazenamento físico de logs, podemos fazer backup dos logs do arquivo de log ldf e recuperar o banco de dados ao usar o arquivo de backup de log.
• O SQL Server lerá o log do arquivo de log ldf toda vez que for iniciado. Irá rolar os assuntos de backup não submetidos antes e avançar com os assuntos que foram submetidos, mas não foram gravados de arquivos de dados para garantir a integridade dos assuntos.
• Um backup completo contém todos os dados do banco de dados, mas não inclui todos os registros. O backup completo contém apenas todas as páginas de dados e logs no final do banco de dados atual.

Log de atividades significa registros de log dos assuntos atuais. Log virtual significa unidade de armazenamento lógico do log. O SQL Server divide logicamente o espaço do arquivo de log ldf em várias partes. Cada peça é um log virtual. Truncamento de log significa excluir o log anterior.

Modelo de Recuperação Simple

Quando você escolhe o modelo de recuperação simple, o SQL Server mantém apenas uma quantidade mínima de informações no log de transações. O SQL Server trunca o log de transações sempre que o banco de dados atinge um ponto de verificação de transação, não deixa entradas de log para fins de recuperação de desastre.

Nos bancos de dados que usam o modelo de recuperação simple, você pode restaurar somente backups completos ou diferenciais. Não é possível restaurar esse banco de dados para um determinado ponto no tempo, você só pode restaurá-lo para a hora exata em que ocorreu um backup completo ou diferencial. Portanto, você perderá automaticamente quaisquer modificações de dados feitas entre a hora do backup completo/diferencial mais recente e a hora da falha.

Recuperação Simple requer menos administração. É mais fácil de gerenciar do que os modelos Full ou Bulk-Logged, mas às custas de maior exposição à perda de dados se um arquivo de dados estiver danificado. A Recuperação Simple não é uma escolha apropriada para sistemas de produção em que a perda de alterações recentes é inaceitável. Ao usar Recuperação Simple, o intervalo de backup deve ser longo o suficiente para impedir que a sobrecarga de backup afete o trabalho de produção, ainda que seja curto o suficiente para impedir a perda de quantidades significativas de dados.

Vantagem: permite operações de cópia em massa de alto desempenho. Recupera o espaço de log para manter os requisitos de espaço pequenos.

Desvantagem: alterações desde o banco de dados mais recente ou backup diferencial deverão ser refeitas.

Modelo de Recuperação Full

Com o modelo de recuperação full, o SQL Server preserva o log de transações até que você faça o backup. Isso permite que você crie um plano de recuperação de desastre que inclua uma combinação de backups de banco de dados completos e diferenciais em conjunto com backups de log de transação.

Você tem a maior flexibilidade para restaurar bancos de dados ao usar o modelo de recuperação full quando ocorre uma falha no banco de dados. Além de preservar as modificações de dados armazenadas no log de transações, o modelo de recuperação full permite restaurar um banco de dados para um ponto específico no tempo.

Vantagem: nenhum trabalho é perdido devido a um arquivo de dados perdido ou danificado. Pode recuperar a um ponto arbitrário no tempo.

Desvantagem: se o log estiver danificado, alterações desde o backup de log mais recente deverão ser refeitas.

Modelo de Recuperação Bulk-logged

O modelo de recuperação bulk-logged é um modelo de finalidade especial que funciona de maneira semelhante ao modelo de recuperação full. A única diferença está na maneira como ele lida com as operações de modificação de dados em massa. O modelo bulk-logged registra essas operações no log de transações ao usar um registro técnico conhecido como registro mínimo. Isso economiza significativamente no tempo de processamento, mas impede que você use a opção de restauração ponto-no-tempo.

Vantagem: permite operações de cópia em massa de alto desempenho, espaço mínimo de log é usado por operações em massa.

Desvantagem: se o log estiver danificado ou operações em massa ocorreram desde o backup de log mais recente, as alterações desde o último backup deverão ser refeitas.

Os modelos de recuperação Full e recuperação Bulk-Logged fornecem a maior proteção para os dados. Esses modelos dependem do log de transações para fornecer recuperabilidade completa e evitar perda de trabalho na mais ampla gama de cenários de falha. O modelo Bulk-Logged fornece maior desempenho e menor consumo de espaço de log para determinadas operações de grande escala.

Quando você escolhe diferentes estratégias de backup do MS SQL Server, você terá limitações específicas para recuperar backups do SQL Server.