Qual Recovery Model utilizar?

Como ultimamente tenho recebido diversos questionamentos em relação à esse tema, resolvi blogar para compartilhar com os demais.

“Qual recovery model que devo utilizar na minha base de dados?” ou ainda “Qual a diferença entre eles?” são dúvidas ainda frequentes para muitos DBAs. No entanto, antes de responder, é necessário explicar um pouco melhor como o log funciona.

Um banco de dados é formado por arquivos de dados e de log. Para entender o que o arquivo de log armazena, é importante falarmos sobre transação. Para cada alteração de dados solicitada, é efetuada uma transação. No início de uma transação, o SQL Server obtém os dados que o usuário deseja trabalhar dos arquivos físicos de dados (datafiles) e os armazena na memória (buffer cache). Uma vez que a transação é confirmada (COMMIT), os dados são confirmados em memória e em um segundo momento, esses dados são alterados no disco (checkpoint). Esse processo é realizado dessa maneira pois o acesso à memória é muito mais rápido que o disco. Portanto, o arquivo de log (transaction log) armazena as instruções Transact-SQL executadas até o momento do checkpoint.

O recovery model é uma configuração realizada para cada banco de dados. O tipo de recovery model utilizado, determina como os dados transacionais daquela base de dados serão protegidos e recuperados, quando necessário. Com isso, é possível recuperar as transações, tanto no momento de backup e restore, quanto durante o processo de recovery do SQL Server.

Existem três tipos de recovery model: simple, full, bulk-logged. A seguir, seguem os principais critérios para escolha de cada um deles. É importante lembrar que o tipo de backup e restore interfere diretamente na opção escolhida.

Quando utilizar Simple Recovery Model

Utilize se os itens a seguir forem verdadeiros:

• Se o banco de dados for perdido ou danificado, você está disposto a perder todas as atualizações que ocorreram no período entre a falha e o backup anterior.

• Estiver disposto a perder alguns dados no log.

• Prefere exclusivamente os backups full e diferenciais, não efetuando backups ou restores de log de transações.

Quando utilizar Full Recovery Model

Utilize o full, ou também o bulk-logged recovery model, se os itens a seguir forem verdadeiros:

• É necessário a recuperação de todos os dados, inclusive os da transação

• Se o banco de dados contém diversos filegroups e deseja-se restaurar os filegroups Read/Write secundários e, opcionalmente, os filegroups read-only

• É necessário recuperar os dados até o ponto de falha

• Deseja-se restaurar páginas de dados individuais

• Está disposto a suportar os custos administrativos relacionados ao backup de log

Quando utilizar Bulk-Logged Recovery Model

O bulk-logged recovery model é recomendado para utilização apenas nos períodos em que estiver executando operações de carga, além de não ser necessário recuperar os dados no exato ponto de falha. Recomendamos que ao finalizar as operações de carga, o recovery model da base de dados seja alterada imediatamente para full novamente.

Espero ter ajudado!  Até o próximo post

Maiores informações:

Recovery Model Overview
https://msdn.microsoft.com/en-us/library/ms189275(v=SQL.100).aspx
Choosing the Recovery Model for a Database
https://msdn.microsoft.com/en-us/library/ms175987(v=SQL.100).aspx
Backup Under the Simple Recovery Model
https://msdn.microsoft.com/en-us/library/ms191164(v=SQL.100).aspx
Backup Under the Full Recovery Model
https://msdn.microsoft.com/en-us/library/ms190217(v=SQL.100).aspx

Comments

• Anonymous
  January 01, 2003
  The comment has been removed
• Anonymous
  August 29, 2011
  Renata, muito legal o post. Tenho uma pergunta, o que você acha em relação a mudar o recovery model para bulk logged durante o período de rebuild online dos índices e depois voltar para FULL. Alguma recomendação em relação a esta prática? A ideia é fazer proveito do rebuild online como uma operação como minimal logged. Abs e parabéns pelo Blog.