É fato que toda empresa, independente de seu porte, gera e armazena muitos dados e informações. Infelizmente também é fato que muitas não se preocupam em criar cópias de segurança e garantir a recuperabilidade destas informações.
Como é consenso entre muitos profissionais, empresários e população em geral, a informação é um dos bens mais preciosos de uma empresa (e é tão suscetível a falhas) e deve ser tratada e armazenada com muito cuidado e atenção.
Em um blog post antigo, falo um pouco sobre a questão de se ter bons backups e possibilidades de restores. “BACKUP – Você está fazendo corretamente?“, faça uma visita =).
A grande maioria dos ambientes que utiliza-se do Microsoft SQL Server para armazenar seus dados, não precisa das soluções mais complexas de backup para garantir a recuperabilidade de seus dados e informações, até mesmo com possibilidades de recuperação no ponto da falha ou em algum ponto no tempo. Com isto, desenvolvi uma Stored Procedure (SP) que atende de forma fácil, simples e objetiva os três principais tipos de backups disponíveis no SQL Server:
- Backup FULL
- Backup DIFF (Differential)
- Backup LOG (Transaction Log)
Para a execução da stored procedure sp_backup basta executá-la à partir da base de dados master passando os seguintes parâmetros:
Exec master..sp_backup @NomeBanco = ‘master’, @TipoBackup = ‘FULL’, @IsCopyOnly = 0, @Caminho = ‘C:TEMP’
- @NomeBanco: Parâmetro do tipo Varchar (100) recebe o nome do banco de dados que será “backapeado” (sic)
- @TipoBackup: Parâmetro do tipo Varchar (20) recebe o tipo de backup que será realizado. Aceita como entrada os valores: ‘Log’, ‘FULL’,’FUL’,’DIF’,’DIFF’, NULL . Se for passado NULL para este parâmetro, o valor default ‘FULL’ é assumido.
- @IsCopyOnly: Parâmetro do tipo bit. Aceita como entrada os valores 0 , 1. Utilizado para informar se no backup será utilizado ou não a opção COPY_ONLY
- @Caminho: Parâmetro do tipo Varchar (200), recebe como entrada o caminho onde o backup será salvo. Aceita entrada com valores nos formatos ‘C:Temp’,’C:Temp’ – Se for passado NULL, é assumido o caminho default da instância.
O resultado será a geração de um arquivo no seguinte formato:
C:tempMASTER_FULL_28_10_2015_22_48_19.bak
Sendo a composição do nome do arquivo:
- C:tempMASTER_FULL_28_10_2015_22_48_19.bak
- Caminho onde o backup foi salvo
- C:tempMASTER_FULL_28_10_2015_22_48_19.bak
- Banco de dados que foi “Backapeado” (sic)
- C:tempMASTER_FULL_28_10_2015_22_48_19.bak
- Tipo de Backup que foi gerado
- C:tempMASTER_FULL_28_10_2015_22_48_19.bak
- Data de geração do backup, no formato dia_mês_ano
- C:tempMASTER_FULL_28_10_2015_22_48_19.bak
- Horário da geração do backup (início do backup) no formato hora_minuto_segundo
- C:tempMASTER_FULL_28_10_2015_22_48_19.bak
- Extensão utilizada para gerar o backup (.bak para FULL e DIFF, .trn para LOG)
Solução para Backup de seu ambiente
Abaixo uma breve descrição de um exemplo de implementação para backup de um ambiente com possibilidade de restore point in time bem como execução dos três tipos de backup supracitados.
Ambiente fictício:
- Windows Server 2012 R2
- Instância SQL Server 2012
- 3 Bancos de dados de usuário (BD01 – BD02 – BD03)
Realização de BACKUP FULL
Backup FULL de todas as bases de usuários
- Crie um Job chamado DBA_BACKUP_FULL com o agendamento para execução todos os domingos às 02:00 da manhã.
- Crie um step para o job do tipo Transact SQL
- Insira um código semelhante ao mostrado a seguir:
- Exec master..sp_backup @NomeBanco = ‘BD01’, @TipoBackup = ‘FULL’, @IsCopyOnly = 0, @Caminho = ‘F:SQLSERVERBACKUP’
- Exec master..sp_backup @NomeBanco = ‘BD02’, @TipoBackup = ‘FULL’, @IsCopyOnly = 0, @Caminho = ‘F:SQLSERVERBACKUP’
- Exec master..sp_backup @NomeBanco = ‘BD03’, @TipoBackup = ‘FULL’, @IsCopyOnly = 0, @Caminho = ‘F:SQLSERVERBACKUP’
Realização de BACKUP DIFERENCIAL
Backup diferencial de todas as bases de usuário
- Crie um Job chamado DBA_BACKUP_DIFF com o agendamento para execução toda quarta feira às 02:00 da manhã.
- Crie um step para o job do tipo Transact SQL
- Insira um código semelhante ao mostrado a seguir:
- Exec master..sp_backup @NomeBanco = ‘BD01’, @TipoBackup = ‘DIFF’, @IsCopyOnly = 0, @Caminho = ‘F:SQLSERVERBACKUP’
- Exec master..sp_backup @NomeBanco = ‘BD02’, @TipoBackup = ‘DIFF’, @IsCopyOnly = 0, @Caminho = ‘F:SQLSERVERBACKUP’
- Exec master..sp_backup @NomeBanco = ‘BD03’, @TipoBackup = ‘DIFF’, @IsCopyOnly = 0, @Caminho = ‘F:SQLSERVERBACKUP’
Realização de BACKUP T-LOG
Backup diferencial de todas as bases de usuário
- Crie um Job chamado DBA_BACKUP_LOG com o agendamento para execução diária com repetições a cada 2 horas de segunda à sexta feita.
- Crie um step para o job do tipo Transact SQL
- Insira um código semelhante ao mostrado a seguir:
- Exec master..sp_backup @NomeBanco = ‘BD01’, @TipoBackup = ‘LOG’, @IsCopyOnly = 0, @Caminho = ‘F:SQLSERVERBACKUP’
- Exec master..sp_backup @NomeBanco = ‘BD02’, @TipoBackup = ‘LOG’, @IsCopyOnly = 0, @Caminho = ‘F:SQLSERVERBACKUP’
- Exec master..sp_backup @NomeBanco = ‘BD03’, @TipoBackup = ‘LOG’, @IsCopyOnly = 0, @Caminho = ‘F:SQLSERVERBACKUP’
Disclaimer:
A SITUAÇÃO ACIMA É APENAS A REPRESENTAÇÃO DE UM EXEMPLO DE USO DA SP_BACKUP. O USO INDEVIDO DESTA STORED PROCEDURES PODE INCORRER EM FALHAS E AUSÊNCIA DE BACKUP CASO NÃO SEJA CONFIGURADO CORRETAMENTE. SEMPRE TESTE QUALQUER SCRIPT EM UM AMBIENTE CONTROLADO ANTES DE COLOCÁ-LO EM PRODUÇÃO. UTILIZE POR SUA CONTA E RISCO.
Gostou? Quer testar?
CLIQUE PARA DOWNLOAD DO SCRIPT
Se você baixou, testou, aprovou, reprovou, odiou, amou ou fez qualquer coisa com este script, por favor deixe seu feedback, crítica, comentário ou sugestão na sessão de comentários.
OBSERVAÇÃO 1:
- A stored procedure sp_backup está disponível gratuitamente para download para quaisquer tipos de uso, seja
pessoal ou comercial, porém apenas solicito que a mesma não seja alterada, e que sejam mantidos os créditos sempre
que esta for ser utilizada. - Compatível com as versões 2008, 2008 R2, 2012 e 2014 do MS SQL Server.
OBSERVAÇÃO 2:
- Qualquer sugestão de melhoria, ou correção de eventuais bugs, por favor envie para meu email: [email protected]
Muito Obrigado.
Edvaldo Castro
Mestre Edvaldo, primeiro parabéns por compartilhar seu conhecimento.
Mas tenho uma duvida/problema e gostaria de saber se vc poderia me auxiliar.
é assim, meu server de SQL utiliza a versão WEB Edition, e por isso quando vou executar a procedure ela volta com um erro dizendo que: “BACKUP DATABASE WITH COMPRESSION não é suportado em Web Edition (64-bit). [SQLSTATE 42000]”
Teria uma maneira de contornar isso?
Agora tenho uma sugestão:
– Você poderia adicionar na Procedure uma variavél que “Valide” o Backup. Isso seria bacana!!!
Novamente Valew por compartilhar seu conhecimento.
Ola Wagner, obrigado pelos parabens e por vir ate meu blog.
quando a versao do seu SQL Server ser incompativel com o COMPRESSION, voce so precisa editar a stored procedure e remover essa opcao, deve funcionar sem maiores problemas.
Vou analisar como fazer a validacao opcional do backup e quem sabe mandar uma nova versao em breve.
Grande abraco
Edvaldo
Conheci seu blog hoje e estou aprendendo muito com todo o conteúdo, principalmente da questão do trabalho na Irlanda.
Trabalho mais rotineiramente com Oracle, e estou me aprofundando em sql server, que também voltei a trabalhar.
Seu blog foi o mais esclarecedor de todos os que encontrei na web.
Gostaria de agradecer e desejo sucesso para você.
Grande abraço.
Ola Marcelo,
Muito obrigado pela leitura do meu blog e comentário. Fico feliz em poder ajudar de alguma forma. Infelizmente parei de escrever ha algum tempo, mas pretendo voltar as atividades do blog em breve.
Grade abraco e sucesso.
Edvaldo Castro
Ficou top Edvaldo!
#TocaRaul
Valeu….
=)
Eae Edvaldo, blz?
Cara, parabéns pela rotina. Agora gostaria de saber se você tem uma rotina pra realizar o expurgo desses backups, se sim, como executaria no seguinte cenário, backup full semanal e backups diffs e logs diários.
Valeu
Amaury
Olá Amaury,
Primeiramente, muito obrigado pela visita ao meu blog e pelos parabéns, fico feliz que tenha gostado.
Quanto à sua pergunta, existem diversas possibilidade para se configurar este expurgo…
Você poderia utilizar por exemplo: Powershell, Forfiles (cmd), algo em dos, ou mesmo T-SQL
Abaixo, um exemplo em T-SQL, que você pode configurar em um job para executar em qualquer periodicidade que vc desejar.
O script deste exemplo, vai apagar os arquivos com as extensões .bak e .trn no diretório E:SQLServerBACKUP e subdiretórios com data de criação maiores que os dias especificados no parâmetro @data.
declare @data datetime = getdate() – 30
EXECUTE master.dbo.xp_delete_file 0,N’E:SQLServerBACKUPS’,N’.bak’,@data,1
EXECUTE master.dbo.xp_delete_file 0,N’E:SQLServerBACKUPS’,N’.trn’,@data,1
Espero ter ajudado
Abraço,
Edvaldo Castro