Fala, pessoal!

Continuando a nossa série sobre o Dbatools, hoje mostrarei um comando que utilizo bastante no meu dia a dia como DBA. E como DBA, sempre temos de estar preparados para realizar uma operação de Restore, seja devido a um problema ou uma solicitação da equipe de desenvolvimento para atualizar os dados em homologação.

Caso ainda não tenha lido os artigos anteriores:

• Dbatools – Parte 01
• Dbatools – Parte 02: Export-DbaScript
• Dbatools – Parte 03: Test-DbaLastBackup

Uma operação de Restore parece ser relativamente simples na maioria dos casos, mas existem casos onde precisamos de muita atenção e esforço.

Por exemplo: um restore ‘Point-in-time’ onde temos que voltar a base de dados para um ponto ou horário especifico e você tem 1000 arquivos de backups para serem restaurados. Parece ser um serviço bem trabalhoso – e de fato, é!

Vamos pensar no seguinte ambiente: eu tenho uma base de dados chamada ‘SQLDAYES’. Para essa base, tenho a seguinte política de backup:

• Backup FULL semanal, realizado todos os domingos, às 23h00
• Backup diferencial, realizado diariamente às 23h00
• Backup de log a cada 10 minutos
• Tenho um problema no meu banco de dados sexta-feira, às 18h00. Preciso restaurar esse banco de dados até 17h50 para voltar ao ponto antes do problema.

A ordem correta para aplicarmos nosso restore, seria:

• Restaurar o FULL do último domingo
• Restaurar o último DIFF, que seria o de quinta-feira, 23h00
• Restaurar todos os logs das 23h00 até as 17h50

Ok, parece simples e serão poucos arquivos, mas as coisas podem ficar piores. Você percebeu que não tinha uma rotina de teste de backup e quando foi verificar seu último FULL estava corrompido – sendo assim, a sequência acima não funcionará e você terá que seguir a seguinte ordem:

• Restaurar o backup FULL do penúltimo domingo. Ou seja, de duas semanas atrás
• Restaurar o último DIFF antes do FULL corrompido, que seria o DIFF do último sábado, às 23h00
• Restaurar todos os logs de sábado das 23h00 até sexta-feira, 17h50

Já imaginou a quantidade de arquivos? Agora parece um pouco mais trabalhoso, não?

Para simular essa operação, criarei um ambiente. Porém, não terá exatamente o cenário acima com backups de duas semanas, mas uma vez que você tenha compreendido a sequência que iremos realizar, ficará fácil de entender.

Primeiramente, se você ainda não conhece essa fantástica solução tanto para backup quanto para manutenção de índices e estatísticas do Ola Hallengren, fica como dever de casa.

Quando configuramos a solução de backup do Ola Hallengren, temos, por default, a estrutura de pastas abaixo:

• [Nome da instancia SQL Server]\[Nome da base de dados]\[Full]
• [Nome da instancia SQL Server]\[Nome da base de dados]\[Diff]
• [Nome da instancia SQL Server]\[Nome da base de dados]\[Log]

Nome do arquivo: [Nome da instancia SQL Server]_[Nome da base de dados]_[Data]_[Hora].

Para meu ambiente eu tenho quatro backups FULL. Primeiro às 22h43 do dia 03/09 e o último às 08h00 do dia 04/09:

Tenho sete backups diferenciais. Primeiro, às 22h44 do dia 03/09 e o último às 09h00 do dia 04/09.

E 64 backups de log. Primeiro, às 22h45 do dia 03/09 e o último às 09h10 do dia 04/09.

Preciso voltar meu banco de dados SQLDAYES até as 09h08 do dia 04/09. No cenário ideal eu precisaria restaurar apenas o último FULL das 08h00; o último DIFF das 09h00, e o último log das 09h10, para poder voltar especificamente às 09h08. Seria um ótimo cenário e com três comandos estaria pronto.

RESTORE DATABASE [Restore_SQLDAYES] FROM
DISK = N’E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\FULL\DESKTOP-A7S2JPV$SQLSERVER2016_SQLDAYES_FULL_20180904_080001.bak’ WITH FILE = 1, MOVE N’SQLDAYES’ TO N’E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\DATA\Restore_SQLDAYES.mdf’, MOVE N’SQLDAYES_log’ TO N’E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\DATA\Restore_SQLDAYES_log.ldf’,
NORECOVERY, NOUNLOAD, STATS = 10

RESTORE DATABASE [Restore_SQLDAYES] FROM DISK = N’E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\DIFF\DESKTOP-A7S2JPV$SQLSERVER2016_SQLDAYES_DIFF_20180904_090001.bak’ WITH FILE = 1, MOVE N’SQLDAYES’ TO N’E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\DATA\Restore_SQLDAYES.mdf’, MOVE N’SQLDAYES_log’ TO N’E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\DATA\Restore_SQLDAYES_log.ldf’,
NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [Restore_SQLDAYES] FROM DISK = N’E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\LOG\DESKTOP-A7S2JPV$SQLSERVER2016_SQLDAYES_LOG_20180904_091000.trn’ WITH FILE = 1, MOVE N’SQLDAYES’ TO N’E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\DATA\Restore_SQLDAYES.mdf’, MOVE N’SQLDAYES_log’ TO N’E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\DATA\Restore_SQLDAYES_log.ldf’,
NOUNLOAD, STATS = 10, STOPAT = N’09/04/2018 09:08:00′

Comprovado: com apenas três comandos eu tenho minha base de dados restaurada até o ponto das 09h08, mas ainda tivemos que decifrar quais backups precisaram ser restaurados.

Já podemos usar o Dbatools nesse caso. O comando Restore-DbaDatabase nos proporciona uma variedade de opções para restore de banco de dados, facilitando nossas vida, onde você pode restaurar centenas de arquivos de backup com apenas um comando, sem se preocupar com a sequência correta dos arquivos – apenas especificando o ponto que deseja parar.

$RestoreTime = Get-Date(’09:08 04/09/2018′)

Restore-DbaDatabase -SqlInstance DESKTOP-A7S2JPV\SQLSERVER2016 -Path ‘E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\’ -DestinationFilePrefix ‘Restore_’ -RestoredDatabaseNamePrefix ‘Restore_’ -MaintenanceSolutionBackup -DestinationLogDirectory ‘E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\DATA\’ -DestinationDataDirectory ‘E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\DATA\’ -RestoreTime $RestoreTime

Com o comando acima, eu preciso apenas especificar a pasta onde estão meus backups. Ele se encarrega de seguir a ordem correta dos arquivos e irá restaurar todos os bancos de dados encontrados dentro das pastas, ou você pode especificar apenas o banco de dados desejado.

Para este caso, tenho apenas arquivos de um banco de dados, então não especificarei o nome. Estou usando os seguintes parâmetros:

• -SqlInstance: instância de SQL Server
• -Path: caminho onde estão meus backups
• -DestinationFilePrefix: será adicionado um prefixo nos arquivos de dados e log durante o restore. Isso facilita para fazer um restore com um nome diferente do banco original
• -RestoredDatabaseNamePrefix: mesmo caso acima, porém, adiciona um prefixo no nome do banco de dados para não sobrescrever o banco original
• -MaintenanceSolutionBackup: esse parâmetro é bem legal – indica que eu tenho uma rotina de backup como a Ola Hallengreen e entende que meus backups estão armazenados em uma estrutura de pastas como mostrado anteriormente. Isso facilita o trabalho da rotina, e consequentemente torna mais rápida a checagem dos arquivos
• -DestinationLogDirectory: diretório onde deseja salvar os arquivos de Log da base de dados restaurada
• -DestinationDataDirectory: diretório onde deseja salvar os arquivos de dados da base dados restaurada
• -RestoreTime: data e hora onde deseja parar o restore (Point-in-time)
• -OutputScriptOnly: apenas gerar os scripts

Porém, nem tudo são flores. O backup FULL das 08h00 e o das 04h00 estão corrompidos, e o desespero bate em sua porta.

Se eu não posso voltar o FULL das 08h00 e nem o das 04h00, eu preciso do FULL das 00h00. Logo, eu posso voltar apenas o DIFF das 03h00, pois os demais arquivos acima das 04h00 são baseados no FULL das 04h00 e não serão aplicáveis, e posteriormente aplicar os logs das 03h00 às 09h10.

No meu caso são apenas 38 arquivos, mas pense no cenário que mencionei lá em cima, com backup FULL uma vez por semana, um DIFF diário e LOG a cada 10 minutos, com os dois últimos FULL corrompidos. Será um trabalho árduo – em casos reais já cheguei a restaurar mais de 1000 backups de Log (deixe nos comentários o seu recorde).

Com o comando Restore-DbaDatabase, podemos voltar toda essa cadeia de backups com apenas um comando.

$RestoreTime = Get-Date(’09:08 04/09/2018′)

Restore-DbaDatabase -SqlInstance DESKTOP-A7S2JPV\SQLSERVER2016 -Path ‘E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\’ -DestinationFilePrefix ‘Restore_’ -RestoredDatabaseNamePrefix ‘Restore_’ -MaintenanceSolutionBackup -DestinationLogDirectory ‘E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\DATA\’ -DestinationDataDirectory ‘E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\DATA\’ -RestoreTime $RestoreTime   -OutputScriptOnly | Out-File ‘C:\Temp\Restore.sql’

Adicionei o parâmetro -OutputScriptOnly para fazer a exportação do script ao invés de executar, após o Pipeline Out-File ‘C:\Temp\Restore.sql’, exportando o script para um arquivo com extensão .sql.

Pronto! Tenho o script do comando completo de Restore para voltar meu banco de dados até as 09h08, ou posso executá-lo direto, conforme resultado abaixo:

Banco de dados restaurado com sucesso, registro do último Log aplicado onde informa que a opção NoRecovery foi ‘Falsa’. Isso indica que deixou nosso banco de dados operacional e pronto para uso.

Posso utilizar para automatizar uma rotina de restore que ocorre com frequência, como solicitações de desenvolvedores.

Get-DbaBackupHistory -SqlServer DESKTOP-A7S2JPV\SQLSERVER2016 -Databases SQLDAYES | Restore-DbaDatabase -SqlServer DESKTOP-A7S2JPV\SQLSERVER2016_02 -useDestinationDefaultDirectories -IgnoreLogBackup -WithReplace

O comando acima está utilizando o Get-DbaBackupHistory para ler todo o histórico de backups do banco de dados SQLDAYES na instância SQLSERVER2016 e passando via Pipeline para o comando Restore-DbaDatabase, que utilizará essas informações para restaurar o banco de dados SQLDAYES na instância SQLSERVER2016_02.

Resultado do comando Get-DbaBackupHistory:

Resultado do comando completo:

Você pode utilizar o trecho abaixo para exportar o resultado em um arquivo .txt:

| Out-File ‘C:\temp\RestoreResult.txt’

Dica bônus: uma dica muito legal de PowerShell é a utilização do parâmetro -WHATIF, o famoso “Mas e se” – muito útil para testar comandos complexos ou duvidosos. Quando informado o parâmetro WHATIF, ele apenas testa o comando e retorna o que acontecerá quando esse comando for executado.

Vamos supor que eu gostaria de apagar os arquivos de uma pasta que tenha ‘Log’ no seu nome. Pela imagem abaixo eu tenho dois arquivos:

Desenvolvi o comando abaixo, mas estou um pouco em dúvida quanto a execução.

Get-ChildItem ‘E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\DATA’ | Where-Object Name -Like ‘*log*’ | Remove-Item

Então eu posso executar o comando Remove-Item com o parâmetro -WhatIf:

What if: Performing the operation “Remove File” on target “E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\DATA\Logs.txt”.
What if: Performing the operation “Remove File” on target “E:\DESKTOP-A7S2JPV$SQLSERVER2016\SQLDAYES\DATA\LogSQL.txt”.

O resultado é a descrição do que aconteceria, mas o meu comando não foi executado.

Reginaldo, é só isso?

O tema backup é um assunto complexo. Perceba que eu abordei de forma simplista e focando em como recuperar os backups e na utilização do comando, subentendendo que você já conheça as diferenças dos recovery model SIMPLE, BULKED LOGGED e FULL.

Ttambém entenda as diferenças entre backup FULL, Diferencial e Log. Deixarei algumas referências pra você ficar mais situado sobre esse assunto.

O objetivo principal é mostrar comandos que eu utilizo no meu dia a dia e que a partir dessas demonstrações, vocês consigam extrair ideias, levando para o mundo ao seu redor.

O comando Restore-DbaDatabase é completamente parametrizável e dinâmico – pode ser adequado em uma rotina de restore que você já tenha hoje, assim como também pode te ajudar em um momento difícil, onde você precise restaurar centenas de arquivos.

Não se limite aos exemplos apresentados em nenhum artigo. Sempre procure ir além, levando os testes para o seu mundo. Dessa forma, você conseguirá absorver muito mais conhecimento.

Se tiver alguma dúvida referente ao backup\restore, fique à vontade para enviar um e-mail ou deixar seu comentário.

O site do Edvaldo Castro é uma ótima referência para estudos, e quando o assunto é backup, ele é fera!

• https://edvaldocastro.com/backup-full-diario/
• https://edvaldocastro.com/politicabkp/
• https://edvaldocastro.com/backup01/

Modelos de recuperação

• https://docs.microsoft.com/pt-br/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-2017

Até a próxima!

Abraços.