Continuando a linha de posts sobre Backup e já partindo para um intermédio entre Segurança dos dados e Disponibilidade, o foco da vez é o famoso Recovery Model, ou simplesmente Modelo de Recuperação.
Venho escrevendo sobre backup ultimamente pois tenho ciência que é um dos pontos fortes e de mais necessidades no que se refere à Administração de banco de dados, pois uma vez perdidos, sem backup seus dados jamais serão recuperados, e então pouco adianta tunning, segurança de acesso, etc etc…
O Modelo de recuperação de um banco de dados está intimamente ligado ao assunto Backup, visto que baseado no modelo de recuperação configurado para determinada base de dados, você terá ou não possibilidades de recovery bem como uma diferenciação na administração e manutenção desta determinada base.
Antes da criação de uma nova base de dados é necessário bastante planejamento para esta, visto que ela requer diversos cuidados prévios para que não fuja ao controle, como:
- Definição da política de backup a ser adotada para a mesma.
- Definição do Recovery Model a ser configurado.
- Definição de permissões de acesso, entre outras.
Para um bom entendimento sobre Recovery Model, e porque este deve ser tratado “com carinho”, segue minhas considerações sobre cada um dos três tipos:
Recovery Model “Simple”
- Este é o modelo que requer menor esforço administrativo relacionado ao controle do tamanho do log de transações, porém é o que mais expõe o banco ao qual está configurado à perda de dados, este Recovery Model não permite backup do log de transações e consequentemente não permite restaurações além do momento do término do backup (Full ou Diferencial).
- Benefícios e aplicação: Pelo motivo de as transações não serem logadas, não requer administração ativa do log de transações, aplica-se este Recovery Model principalmente em ambientes de Homologação, Testes e Desenvolvimento, desde que estes permitam (com base no negócio) que haja perda de dados não presentes em backups previamente realizados.
- Principais Riscos: Deve-se ter total atenção, para erroneamente não configurar este modelo de recuperação em ambientes de produção com a finalidade de contenção do tamanho do arquivo de log de transações (.ldf), pois como é conhecida uma famosa frase no meio dos DBAs: “DBA Administra banco de dados, Milagre a gente não faz”.
Recovery Model “Bulk-Logged”:
- Certamente um dos Modelos de Recuperação mais complexos e de menor entendimento presentes no SQL Server, este permite que operações de Bulk-Insert sejam minimamente logadas, ou seja, operações de inserção de grande massa de dados, caso fossem logadas normalmente aumentariam o tamanho do log de transações de modo que este poderia fugir ao controle, estourar espaço em disco, dentre outros problemas.
- Benefícios e aplicação: Quando há grandes e frequentes inserções de massa de dados, conforme previamente citado, há um grande benefício na utilização do modelo de recuperação “Bulk-Logged”, pois este evita que o Administrador da base de dados perca o controle do tamanho do log de transações.
- Advertência: Como não existe “almoço grátis”, paga-se um preço por esta “mágica” realizada pelo recovery model Bulk-Logged. Embora o arquivo de Log de Transações (.ldf) não cresça com a inserção de dados em massa (Bulk Operations), o backup de log levará consigo todos os extents (conjunto de 8 páginas de dados) que contem páginas alteradas nestas operações, logo é vantajoso utilizá-lo por não aumentar o tamanho do arquivo, porém é necessário ter em mente que seu backup de log conterá não todas as páginas, mas todos os extents que tiveram páginas alteradas. Portando, utilize-o com moderação.
Recovery Model “Full”
“Com grandes poderes vem grandes responsabilidades” – (Benjamin Parker).
- Não há dúvidas de que este seja o mais poderoso dentre todos os modelos de recuperação no SQL Server, o que te permite maior segurança em relação à não exposição a perda de dados. Em contrapartida, é o que existe maior esforço administrativo e atenção quando à manutenção de um banco com teste modelo configurado.
- Benefícios: É possível que sua exposição à perda de dados seja bem próximo a zero, uma vez que este modelo de recuperação permite a realização de restores em um ponto específico no tempo, restore em pontos prévios à falhas, restore com auxílios do backup tail do log, dentre outros.
- Principais Riscos: Tendo configurado o modelo de recuperação Full, obrigatoriamente deve-se realizar frequentemente o backup do log de transações, uma vez que para garantir toda este leque de opções de recuperação, as transações são logadas e fazem com que o arquivo de log de transações cresça a medida que o banco é utilizado. O overhead administrativo é muito maior em relação aos demais modelos de recuperação. Caso não se tenha o cuidado de fazer backups de logs frequentemente, ou por exemplo um job de backup falhe, corre-se um grande risco de estouro de espaço e com isto, o SQL Server fica impossibilidade de gravar novas transações, uma vez que não terá espaço suficiente para registrar estas transações no arquivo de log.
Este foi apenas um overview sobre “RECOVERY MODEL” no SQL Server, quaisquer dúvidas ou sugestões fique a vontade para comentar, criticar, elogiar ou o que quer que tenha vontade de escrever no espaço para comentários….
Abraços, e obrigado pela leitura.
Edvaldo Castro
@edvaldocastro
Olá Edvaldo,
Parabens pelo blog e pela linguagem simples que usa..
Minhas duvidas são: ( de ante mão te peço desculpas por estas duvidas)
1 – Aqui na empresa o backup é modo Simples, então adotei backpu FULL da base uma vez ao dia, e diferencial de hora em hora… Posso mudar para modo de recuperação FULL sem problemas ?? faço isso fora do expediente, certo ?
2 – Com a alteração para FULL permaneço fazendo backup FULL da base uma vez ao dia e o incremental de uma em uma hora acrescentando o backup de LOG em que periodicidade ?
Desculpas por tantas duvidas….
Obrigado pela atenção!
Olá Fábio,
Primeiramente, muito obrigado pela visita e fico feliz que tenha gostado. Não se desculpe, dúvidas são saidas e é sempre um imenso prazer ajudar sempre que posso.
Vamos às resposta (ou tentativa delas).
1 – Fazer uma atividade de alteração no banco de dados fora do horário de expediente, é sempre mais prudente, porém tecnicamente falando, a alteração é feita de modo online na base e não há impacto.
2 – A quantidade de backup varia muito de ambiente para ambiente (na grande maioria dos casoa, por exemplo, eu não cogito backup full diário), uma boa métrica para a periodicidade de seus backups de log, é quanto tempo de dados você pode correr o risco de perder. Isso é muito relativo, pois mesmo configurando por exemplo backup de log a cada hora, é possível restaurar o que não estiver no backup diretamente do arquivo de transaction log não backupeado no momento da falha.
Nestes casos é necessário Avaliar alguns parâmetros como Tamanho da base, Criticidade, utilização, e etc etc…
Uma ideia geral (sem conhecer o ambiente) baseada em sua estrategia atual, talvez seria aumentar a periodicidade do diferencial para uma vez ao dia,e log a cada hora. Talvez também valha o estudo da viabilidade de não necessariamente fazer um full diário..
OBS.: Como falei, é complicado falar uma estratégia sem conhecer o ambiente, veja as sugestões e adapte-as ao seu cenário.
Mais uma vez, obrigado pela visita.
Abraço,
Edvaldo Castro
Edvaldo,
Parabéns pelo blog, e pelo post, no qual, certamente ajudou não sou a mim, mas a vários profissionais.
Abraços.
Legal, Edvaldo, parece que tu leste meus pensamentos… é exatamente o que falo no treinamento de SQL Server.
Vou recomendar teu blog.
Abraço!
Muito Obrigado pelo feedback e recomendação Marcelo…
Aproveitando, farei um WebCast abordando tudo sobre Backup no SQL Server, será dia 05 de Dezembro as 20:30
Link para inscrição: http://goo.gl/DMscg
Abraços
Boa tarde Edvaldo,
Só pra fechar a discussão, o Luciano Moreira me passou o link abaixo.
http://dba.stackexchange.com/questions/6297/is-it-possible-to-restore-sql-server-bak-and-shrink-the-log-at-the-same-time/6421#6421
Testei e deu certo. É muita gambiarra, mas resolveu o meu problema de espaço no HD.rs
Obrigado,
Armstrong
Edvaldo,
Estou com o seguinte problema aqui na minha empresa e gostaria de saber se já passou pela situação e tem alguma dica.
O problema é o seguinte:
Sempre tenho que restaurar bases de clientes para testes de desenvolvedores. Estas bases sempre estão no modo de recovery full onde o log (ldf) crescem indefinidamente. O backup também é feito como full.
Verificando com o filelistonly, um único backup chega por exemplo com 50 GB do MDF e 100 de LDF.
O servidor (Sqlserver 2008) que eu tenho dispõe de apenas 300GB de HD.
Então minha dúvida é se tem como fazer o restore apenas do arquivo MDF, descartando o log ou mesmo recriando um novo arquivo com poucos MBs. Desta forma vou precisar de pouco mais de 50 GB pra voltar o backup e não 150 GB.
Como a demanda é grande ia ajudar bastante e não precisaria toda hora deletar um banco pra conseguir subir outro.
Quando eu tenho o arquivo MDF puro consigo recria o arquivo de log e volto a base sem problemas. Já quando tenho o backup full completo (arquivo BAK )é que vem este ‘problema’.
Obrigado pelo apoio.
Olá Armstrong,
Pelo que entendi, seu problema é em relação ao restore de bases do ambiente de Produção para Desenvolvimento e Testes.
A premissa para você manter um recovery model em FULL, é que seja feito regularmente backup de log da base, isto evita este crescimento descontrolado e garante a recuperabilidade da base, em caso de falha.
Dois pontos devo expor:
1 – Aconselho você a manter este recovery model em FULL somente se estiver fazendo backups de log, caso contrário, de nada adianta manter em full, logar todas as transações, se não for feito backup do Transaction Log.
2 – Caso não seja necessário, e sua rotina de backup contemple apenas Backup FULL, como você mencionou em seu comentário, você pode sem problemas maiores alterar seu recovery model para SIMPLE (Lembrando que você somente poderá recuperar a bases até o ponto onde foi feito o backup full…
Se optar por fazer o backup de log e garantir que o banco possa ser recuperado com maior precisão, em caso de falhas, basta realizar um backup do log (é importante guardar bem este backup) e como seu arquivo de log cresceu além do esperado, você pode executar um ShrinkFile (Apenas no LOG), e em seguida realizar seu backup normalmente.
Após realizar o backup de log, o SQL Server irá truncar o log incluindo todas as transações no arquivo de backup do mesmo, assim, quando for fazer o restore ele irá alocar em disco, o espaço que o arquivo físico está ocupando em disco…
Resumindo, aconselho você a realizar seu backup de log (exceto se o negócio não exigir), Executar ShrinkFile (no LOG), e o backup em seguida…
Espero ter ajudado…
Muito Obrigado pelo contato…
att.
Edvaldo Castro
Obrigado pela resposta!
Ajudou sim a esclarecer. O problema é que como estas bases são de clientes não posso mexer, nem mesmo alterar, já que além de não ter acesso, foge do meu escopo. Posso no máximo repassar todos estes seus conselhos.
Enfim, como consequência da má administração da rotina de backup o arquivo de backup chega pra mim gigante, e como tenho pouco espaço em HD fica difícil gerenciar vários backups de variados clientes.
Achei que o Sqlserver teria então alguma mágica de restaurar este backup sem o LOG que é desnecessário para nosso ambiente de testes (tipo um backup lógico dos dados no Oracle -expdp).
Quando o pessoal consegue trazer o MDF é ótimo, mas pra cópia dele o banco necessita estar parado, aí complica.
Obrigado pelo retorno,
Armstrong
Grande Edis, show de bola. Simples, linguagem clara e objetivo. Congrats bro !!!