Fala, pessoal!

Continuando a série de artigos sobre o Dbatools, hoje irei falar sobre teste de backup. Você já ouviu a paráfrase “Backup: quem tem um, não tem nenhum!”?

Pois é! Ter uma rotina de backup é extremamente importante, mas de que adiantaria apenas fazer os backups e ter suas cópias espalhadas pelo mundo se aquelas cópias não servissem para serem restauradas se necessário? Para isso, um teste de backup frequente das suas cópias é extremamente importante.

Hoje apresentarei um comando do Dbatools Test-DbaLastBackup que facilita seus testes e como podemos automatizar nossas rotinas de teste de backup para SQL Server.

Se você ainda não leu os artigos anteriores sobre o Dbatools, segue os links:

• https://imasters.com.br/banco-de-dados/dbatools-parte-01
• https://imasters.com.br/banco-de-dados/dbatools-parte-02-export-dbascript

O comando Test-DbaLastBackup identifica qual foi o último backup FULL realizado no seu banco de dados, quais foram os últimos backups de Log e restaura todos os arquivos de acordo com a sua sequência. Após a restauração do backup, é aplicado um CHECKTABLE para avaliar a integridade dos objetos dos seus bancos de dados. Tudo isso em um único comando PowerShell.

Vamos lá, vou iniciar preparando o ambiente de teste com o script abaixo:

BACKUP DATABASE SQLDAYES TO DISK = ‘C:\TEMP\SQLDAYES.BAK’

BACKUP LOG SQLDAYES TO DISK = ‘C:\TEMP\SQLDAYESLOG1.BAK’
BACKUP LOG SQLDAYES TO DISK = ‘C:\TEMP\SQLDAYESLOG2.BAK’
BACKUP LOG SQLDAYES TO DISK = ‘C:\TEMP\SQLDAYESLOG3.BAK’

Conforme a imagem acima, tenho um backup FULL e três backups de LOG. Abaixo, um script para listá-los via SQL Server:

SELECT TYPE,ISNULL(S.DATABASE_NAME,’NO BACKUP’) BACKUPFULL, S.BACKUP_START_DATE,NAME,physical_device_name,user_name
FROM MSDB.DBO.BACKUPSET S
INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY M ON S.MEDIA_SET_ID = M.MEDIA_SET_ID
WHERE DATABASE_NAME = ‘SQLDAYES’
ORDER BY S.BACKUP_START_DATE DESC

Também temos algumas opções via Dbatools com o comando Get-DbaLastBackup:

Get-DbaLastBackup -SqlServer DESKTOP-A7S2JPV\SQLSERVER2016 | Out-Gridview

E com o comando Get-DbaBackupHistory:

Get-DbaBackupHistory -SqlServer DESKTOP-A7S2JPV\SQLSERVER2016 -Databases SQLDAYES | Format-Table

Para fazer o teste manualmente precisaríamos aplicar o último FULL “SQLDAYES.BAK” e aplicar os logs nessa sequência: “SQLDAYESLOG1.BAK”, “SQLDAYESLOG2.BAK” e “SQLDAYESLOG3.BAK”. Caso tente restaurar um Log fora dessa sequência, receberemos um erro. Com o comando Test-DbaLastBackup é respeitada a sequência e caso algum arquivo de backup esteja faltando, um alerta será apresentado.

Abaixo, o comando para testar o último backup do meu banco de dados chamado SQLDAYES.

Test-DbaLastBackup -SqlServer DESKTOP-A7S2JPV\SQLSERVER2016 -Databases SQLDAYES

Esse é o resultado que temos com o comando acima:

SourceServer : DESKTOP-A7S2JPV\SQLSERVER2016
TestServer : DESKTOP-A7S2JPV\SQLSERVER2016
Database : SQLDAYES
FileExists : True
Size : 189,33 MB
RestoreResult : Success
DbccResult : Success
RestoreStart : 2018-08-30 16:35:45.351
RestoreEnd : 2018-08-30 16:35:52.276
RestoreElapsed : 00:00:06
DbccStart : 2018-08-30 16:35:52.318
DbccEnd : 2018-08-30 16:35:54.602
DbccElapsed : 00:00:02
BackupDate : {30/08/2018 16:21:37, 30/08/2018 16:21:38, 30/08/2018 16:21:38, 30/08/2018 16:21:38}
BackupFiles : {C:\temp\SQLDAYES.bak, C:\temp\SQLDAYESLOG1.bak, C:\TEMP\SQLDAYESLOG2.BAK, C:\temp\SQLDAYESLOG3.bak}

Passos executados acima:

• Descobrir quais foram os últimos backups
• Restaurar o banco de dados com nome padrão [dbatools-testrestore-NomedoseuDb], modificando o nome lógico dos arquivos para não gerar conflito, e criando os arquivos no diretório padrão indicado para dados e logs na configuração da instância
• Executar a operação DBCC CHECKTABLE
• Apagar o banco de dados criado
• Gerar report das operações

Através de alguns parâmetros podemos alterar o comportamento do comando. Abaixo, alguns parâmetros que podem ser úteis para o seu ambiente:

Test-DbaLastBackup -SqlServer DESKTOP-A7S2JPV\SQLSERVER2016 -DataDirectory “E:\TestRestore\” -LogDirectory “E:\TestRestore\” -MaxMB 8192 -IgnoreLogBackup -NoCheck -VerifyOnly

• MaxMX: aplicando o restore apenas em bancos de dados com até 8GB. Caso você tenha algum banco de dados maior que 8GB, ele será ignorado e você pode pensar em algo customizado para os bancos de dados relativamente grandes.
• LogDirectory e DataDirectory: alterando o caminho onde os arquivos de dados e logs serão criados, caso a unidade padrão não tenha espaço suficiente.
• IgnoreLogBackup: ignorar backups de Log, aplicar apenas o FULL.
• NoCheck: não aplicar a operação DBCC CHECKTABLE.
• VerifyOnly: verifica a integridade do backup utilizando a opção VERIFYONLY, mas não restaura o banco de dados. Pode ser uma boa opção quando não se tem espaço disponível para aplicar o restore completo.

Ok, e como automatizar para rodar no meu ambiente com agendamento e receber um e-mail com o relatório?

O primeiro passo é entender a situação do seu ambiente e adequar os parâmetros corretos no seu script. Verifique se você possuí espaço para aplicar o restore e planeje uma janela adequada para não onerar seu ambiente produtivo.

Agora vamos para a segunda parte: automatizar a rotina para enviar um e-mail com o resultado.

Para enviar o e-mail, utilizaremos o comando Send-MailMessage. Precisamos passar um servidor SMTP e uma credencial para enviarmos um e-mail via PowerShell. Vamos salvar uma credencial em arquivo XML para reutilizarmos depois na chamada do comando.

Get-Credential | Export-Clixml C:\Temp\credemail.xml

Informe suas credencias para conexão com seu servidor de e-mail. Elas serão salvas no arquivo credemail.xml em C:\Temp ou no local que você indicar.

A senha será criptografada dentro do arquivo. Depois disso utilizaremos o script abaixo para testar o backup, capturar o resultado, transformá-lo e enviar por e-mail.

Devido a manipulação de HTML no código PowerShell, vou deixar o link para o script no meu Github:

• https://github.com/Jamal27/SQLServer_Scripts/blob/master/Test-DbaLastbackup.ps1

Abaixo, apenas um trecho que envia o e-mail. Consulte o script completo no link acima.

##Envia E-mail com arquivo .txt em anexo e corpo HTML
Send-MailMessage -Credential $cred -SmtpServer “smtp.office365.com” -To “reginaldo.silva@dataside.com.br” -From “reginaldo.silva@dataside.com.br” -Subject “Test Last Backup – Dbatools” -UseSsl -Body $ResultTestHtml -BodyAsHtml -Attachments ‘C:\temp\ResultEmail.txt’ -Priority High

Após o comando acima ser executado e nossa operação de teste obter sucesso, teremos o seguinte e-mail:

Caso a operação de teste identifique problemas, teremos esse e-mail, conforme abaixo, para gerar um erro. Eu apaguei o arquivo de backup de log “SQLDAYESLOG2.BAK”. Logo, o SQL Server não conseguirá restaurar a sequência até o arquivo “SQLDAYESLOG3.BAK” e irá reportar um alerta.

Porém, em casos de Warning a mensagem vai estar dentro do arquivo .txt em anexo do e-mail.

Para simular um erro na operação de CHECKTABLE, eu gerei uma corrupção na minha base de dados “testecorruption”. Fiz backup sem a opção CHECKSUM, ou seja, minha corrupção agora também foi propagada para meu arquivo de backup.

Muitas vezes só vamos descobrir isso quando precisamos do backup, mas com o Test-DbaLastBackup, será validado na opção DBCC CHECKTABLE, conforme no exemplo abaixo:

Tivemos sucesso na operação de Restore, mas ocorreu uma falha grave na operação de CHECKTABLE. É hora de agir e analisar a corrupção no seu backup – porque backup, quem tem um, não tem nenhum!

Ok. Agora, como posso automatizar essa rotina em um Job SQL Server?

Não irei reinventar a roda. Aqui está passo a passo com detalhes: https://dbatools.io/agent/.

Ótimo, galera! Espero que tenham gostado desse comando, e que essa automação lhes seja muito útil. De qualquer forma, não deixe de testar seus backups frequentemente.

Finalizo por aqui e me siga nessa imersão ao Dbatools.

Abraços!