Automatizar Backups Armazenando em Blob – Azure usando Microsoft SSMS – Éder Lelis

Olá, espero que você esteja bem!

Algumas pessoas me pediram para dar continuação no assunto de Backups para o Azure, daí resolvi escrever este Post, e espero ser bem útil. Vou dividir ele em duas partes, que vou chamar de “automatização de backup” e “backups avulsos”. Portanto se o assunto te agrada, continue aqui e bora para mais um conhecimento bacana!

Para aqueles que querem criar um Job de Backup e automatizar o trabalho pensando principalmente em segurança, afinal, se um dia algo acontecer com meu banco de dados, será que está preparado? Quanto “tempo de dados” posso perder?

Pause para momento merchandising… rsrs!

Sou Consultor DBA SQL Server na Power Tuning. É uma Empresa de Consultoria a qual também pode te ajudar não só com esta questão de automatizar backups, e sim muito mais… Dê uma olhadinha no site e qualquer dúvida, só entrar em contato por lá mesmo. Power Tuning – Liberte o poder dos seus dados!

Voltando pra cá!

1) Automatização de backup

Para criarmos um job de backup vou usar os scripts do OlaHallengren, devido ter uma licença de código aberto popular e amplamente utilizada. Podendo ser usado em Microsoft SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017 e SQL Server 2019.

Vamos iniciar fazendo o download do MaintenanceSolution.sql. Este script vai ser usado para criar todos os objetos e trabalhos que vamos precisar.

Para mais detalhes sobre o script que você acabou de baixar, segue links do site:

Backup do banco de dados: backup do servidor SQL
DatabaseIntegrityCheck: SQL Server Integrity Check
IndexOtimize: SqL Server Index and Statistics Maintenance

Portanto depois de abrir o script em seu Management Studio (SSMS), basta dar F5.

Observa (no lado direito) que é instalado por default na database Master. Alguns parâmetros estão previamente definidos, mas podem ser alterados depois caso queira.

Do lado esquerdo mostra as Procedures que foram criadas.

Pronto, os Jobs também foram criados.

Job de Backup FULL (para as bases do sistema: model, master, msdb)
Job de Backup DIFF
Job de Backup FULL
Job de Backup LOG

Agora vamos editar algumas propriedades do Job DatabaseBackup – USER_DATABASES – FULL.

Vamos editar o step do Job.

Para este nosso lab de hoje vamos usar apenas uma database, no caso a database AdventureWorks2014, portanto basta alterar esse parâmetro.

Mas caso queira fazer o backup de mais de uma database, ou todas as databases exceto uma específica, ou outras opções, veja mais detalhes no próprio site do OlaHallengren.

Veja que além de especificar a database que faremos o backup FULL, também especifiquei o diretório onde vamos salvar nossos backups, que no caso será em um blob no Azure. Feito as alterações basta clicar no OK para salvar as mudanças.

Vamos agora executar o Job manualmente.

Num é que deu erro, e agora? Veja como está fácil de analisar o motivo do erro! 🙂

Segue abaixo a mensagem de erro reportada no Job, pois em texto talvez fique mais fácil localizar o erro.

Job Name		DatabaseBackup - USER_DATABASES - FULL
Step Name		DatabaseBackup - USER_DATABASES - FULL
Duration		00:00:10
Sql Severity	16
Sql Message ID	50000
Operator Emailed	
Operator Net sent	
Operator Paged	
Retries Attempted	0

Message
Executed as user: NT SERVICE\SQLSERVERAGENT. Date and time: 2022-05-19 16:52:22 [SQLSTATE 01000] (Message 50000)  Server: DESKTOP-PKXD [SQLSTATE 01000] (Message 50000)  Version: 15.0.4198.2 [SQLSTATE 01000] (Message 50000)  Edition: Developer Edition (64-bit) [SQLSTATE 01000] (Message 50000)  Platform: Windows [SQLSTATE 01000] (Message 50000)  Procedure: [master].[dbo].[DatabaseBackup] [SQLSTATE 01000] (Message 50000)  Parameters: @Databases = 'AdventureWorks2014', @Directory = 'https://labsbkp.blob.core.windows.net/backup/FULL/', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = NULL, @CleanupMode = 'AFTER_BACKUP', @Compress = NULL, @CopyOnly = 'N', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'Y', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @MinBackupSizeForMultipleFiles = NULL, @MaxFileSize = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTER_BACKUP', @MirrorURL = NULL, @AvailabilityGroups = NULL, @Updateability = 'ALL', @AdaptiveCompression = NULL, @ModificationLevel = NULL, @LogSizeSinceLastLogBackup = NULL, @TimeSinceLastLogBackup = NULL, @DataDomainBoostHost = NULL, @DataDomainBoostUser = NULL, @DataDomainBoostDevicePath = NULL, @DataDomainBoostLockboxPath = NULL, @DirectoryStructure = '{ServerName}${InstanceName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}', @AvailabilityGroupDirectoryStructure = '{ClusterName}${AvailabilityGroupName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}', @FileName = '{ServerName}${InstanceName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}', @AvailabilityGroupFileName = '{ClusterName}${AvailabilityGroupName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}', @FileExtensionFull = NULL, @FileExtensionDiff = NULL, @FileExtensionLog = NULL, @Init = 'N', @Format = 'N', @ObjectLevelRecoveryMap = 'N', @ExcludeLogShippedFromLogBackup = 'Y', @DirectoryCheck = 'Y', ...  Version: 2022-01-02 13:58:13 [SQLSTATE 01000] (Message 50000)  Source: https://ola.hallengren.com [SQLSTATE 01000] (Message 50000)  	 [SQLSTATE 01000] (Message 50000)  The value for the parameter @Directory is not supported. [SQLSTATE 42000] (Error 50000)  	 [SQLSTATE 01000] (Message 50000)  The documentation is available at https://ola.hallengren.com/sql-server-backup.html. [SQLSTATE 42000] (Error 50000)  	 [SQLSTATE 01000] (Message 50000)  Date and time: 2022-05-19 16:52:26 [SQLSTATE 01000] (Message 50000)  	 [SQLSTATE 01000] (Message 50000).  The step failed.

Job Name DatabaseBackup - USER_DATABASES - FULL

Step Name DatabaseBackup - USER_DATABASES - FULL

Duration 00:00:10

Sql Severity 16

Sql Message ID 50000

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted 0

Message

Executed as user: NT SERVICE\SQLSERVERAGENT. Date and time: 2022-05-19 16:52:22 [SQLSTATE 01000] (Message 50000) Server: DESKTOP-PKXD [SQLSTATE 01000] (Message 50000) Version: 15.0.4198.2 [SQLSTATE 01000] (Message 50000) Edition: Developer Edition (64-bit) [SQLSTATE 01000] (Message 50000) Platform: Windows [SQLSTATE 01000] (Message 50000) Procedure: [master].[dbo].[DatabaseBackup] [SQLSTATE 01000] (Message 50000) Parameters: @Databases = 'AdventureWorks2014', @Directory = 'https://labsbkp.blob.core.windows.net/backup/FULL/', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = NULL, @CleanupMode = 'AFTER_BACKUP', @Compress = NULL, @CopyOnly = 'N', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'Y', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @MinBackupSizeForMultipleFiles = NULL, @MaxFileSize = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTER_BACKUP', @MirrorURL = NULL, @AvailabilityGroups = NULL, @Updateability = 'ALL', @AdaptiveCompression = NULL, @ModificationLevel = NULL, @LogSizeSinceLastLogBackup = NULL, @TimeSinceLastLogBackup = NULL, @DataDomainBoostHost = NULL, @DataDomainBoostUser = NULL, @DataDomainBoostDevicePath = NULL, @DataDomainBoostLockboxPath = NULL, @DirectoryStructure = '{ServerName}${InstanceName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}', @AvailabilityGroupDirectoryStructure = '{ClusterName}${AvailabilityGroupName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}', @FileName = '{ServerName}${InstanceName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}', @AvailabilityGroupFileName = '{ClusterName}${AvailabilityGroupName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}', @FileExtensionFull = NULL, @FileExtensionDiff = NULL, @FileExtensionLog = NULL, @Init = 'N', @Format = 'N', @ObjectLevelRecoveryMap = 'N', @ExcludeLogShippedFromLogBackup = 'Y', @DirectoryCheck = 'Y', ... Version: 2022-01-02 13:58:13 [SQLSTATE 01000] (Message 50000) Source: https://ola.hallengren.com [SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000) The value for the parameter @Directory is not supported. [SQLSTATE 42000] (Error 50000) [SQLSTATE 01000] (Message 50000) The documentation is available at https://ola.hallengren.com/sql-server-backup.html. [SQLSTATE 42000] (Error 50000) [SQLSTATE 01000] (Message 50000) Date and time: 2022-05-19 16:52:26 [SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000). The step failed.

Agora vou ensinar algo que já me salvou nas análises de muitas falhas de job, digamos que será um BÔNUS! rsrs

Nem sempre as mensagens que vemos nas falhas dos Jobs no SSMS são completas, ou seja, algumas vezes digamos que “falta algum pedaço”, mostra incompleta, e fica então difícil entender o motivo da falha. Sendo assim, uma boa prática é colocar para após cada execução do Job, salvar uma mensagem digamos que “mais detalhada” do processo. Em caso de falha ou mesmo tendo executado com sucesso.

Vamos lá, que na prática vão entender melhor!

Na tela de edição do Step, tem uma aba Advanced.

Ao clicar, vai ver um campo chamado Output file, que é o local onde vai ser guardado nosso log de execução do Job. Vamos então clicar no “pontinhos” e mudar para um caminho mais familiar.

Coloquei para ser salvo no disco F: e o nome do arquivo será Log_bkpFull.txt

Feito isso, basta salvar clicando no botão OK. Dúvida: Marcar ou não a opção Append output to existing file?

Caso marque esta opção, a cada execução do job vai ser salvo TODO O HISTÓRICO neste arquivo txt, ou seja, se você acha que necessita guardar tudo, basta marcar ela. Porém o seguinte, caso o job execute várias vezes durante o dia, este arquivo vai ter um histórico grande e por isso o bom é lembrar de limpar alguns registro dele de vez em quando, para não ficar um arquivo muito grande. Mas no nosso caso, prefiro deixar desmarcado e ver apenas a mensagem da última execução, ou seja, se na próxima execução o job rodar com sucesso, vai sobrepor o histórico e não vou ter o motivo da falha neste txt.

Vamos agora voltar para a execução do Job e rodar ele novamente para gerar a falha e salvar no nosso arquivo txt.

Pronto, job falhou, agora vamos abrir o arquivo txt.

Veja se visivelmente não ficou bem melhor para identificar o erro?!

The value for the parameter @Directory is not supported. Ou seja o valor do parâmetro @Directory não é suportado.

Vamos voltar no parâmetro e ver onde está o erro, será o endereço do blob? Será alguma permissão?

Voltando na documentação no site, veja onde está o nosso erro…

Na sessão de exemplos diz:

E. Fazer backup de todos os bancos de dados de usuários para o Azure Blob Storage, usando compactação

EXECUTE dbo. Banco de dadosBackup
@Databases = ‘USER_DATABASES’,
@URL = ‘https://myaccount.blob.core.windows.net/mycontainer’,
@Credential = ‘MyCredential’,
@BackupType = ‘FULL’,
@Compress = ‘Y’,
@Verify = ‘Y’

Portanto não podemos usar @Directory, pois nosso backup não será local, precisamos trocar e colocar @URL.

Logo após a alteração, salvar e executar o Job novamente.

Configurei o Microsoft Azure Storage Explorer para ver meu blob. Backup feito com sucesso!

O backup foi feito conforme caminho do blob que colocamos no job: “backup>” nome do servidor/instância+database+FULL e no nome do arquivo acrescentou também a data do dia e a hora.

Visualizando direto pelo Portal do Azure:

Um possível erro pode acontecer na execução deste backup e vamos simular aqui agora, veja este detalhe na Procedure:

Ou seja, é feito um select na credencial para verificar se já existe cadastrada no servidor, e caso não exista, retornará um erro.

Vamos então validar?

select * from sys.credentials where upper (credential_identity)=‘Shared Access Signature’

No meu caso a credencial já estava cadastrada. Mas e se no seu não estiver, como vai continuar agora? Calma, não vou te deixar na mão, vamos então fazer dar errado, dropando a credencial cadastrada.

Vamos agora executar o Job novamente e ver o que acontece.

Ufa, num é que realmente deu erro!

Verificando o erro, veja que a mensagem foi diferente da primeira no início do post.

Cannot open backup device ‘https://labsbkp.blob.core.windows.net/backup/DESKTOP-PKXD/AdventureWorks2014/FULL/DESKT… The step failed.

Agora para dar continuidade no roteiro, vamos passar para o outro tópico.

2) Backups avulsos

O jeito mais fácil que vejo para cadastrar a credencial no SSMS é fazendo um backup avulso. Ou seja, veja que legal, você vai aprender agora a cadastrar sua credencial e/ou se já tem ela cadastrada mas não sabe como fazer um backup para o blob no Azure, irá aprender também.

No meu outro post sobre Restore de Backups Armazenados no Azure usando Microsoft SSMS também ensino a criar e salvar a credencial no servidor. Se ainda não viu, depois dê uma olhada!

Primeiro vamos selecionar a Database que queremos fazer o backup para o blob.

Em seguida vamos em Backup Options para colocar a opção de Compress backup.

Na opção General vai mostrar a Database que será feito o backup e o tipo de backup, que no nosso caso será o FULL. Lembrando que o destino será o blob no Azure portanto URL. Logo em seguida basta clicar no Add.

Se a credencial estivesse já salva, o campo Azure storage container estaria preenchido, daí bastaria dar OK, mas como ainda não temos, precisamos clicar em New container.

Agora precisamos logar na conta do Azure usando o e-mail e a credencial correta, depois irá aparecer o Storage Account e também o Blob Container. Geramos em seguida uma credencial (Shared Access Signature) e definimos uma data de expiração.

Após logar e preencher todos os campos, basta criar uma credencial.

Credencial gerada, copie e salve, pois vai ser muito útil para fazermos backups avulsos.

Tudo feito e pronto para iniciar o backup. Porém uma dica importante, ao invés de já iniciar clicando no OK, recomendo sempre quando tiver em alguma tela do SSMS e aparecer esta opção de “Script“, clique para gerar o script ao invés de clicar no OK. Pois com o script gerado na tela, é melhor para você revisar o código e também para validar se realmente é o que você quer. E também consegue acompanhar a execução (percentual) em alguns casos.

Script gerado, conferido e revisado, pronto, só executar e sucesso!

Verificando no Azure, e tudo correto.

Que tal agora voltar no Job e tentar executar o backup novamente? Bora lá!

Conferindo novamente, backup feito com sucesso.

Este post já está ficando grande, rsrs, mas vou te recompensar, que tal mais um BÔNUS!

Sabia que o blob possui limitação de tamanho de arquivo? Pois é, sendo assim dependendo do tamanho da Database, não conseguirá fazer o backup usando apenas um arquivo, como no nosso exemplo acima AdventureWorks2014_FULL.bak.

Sendo assim pode haver necessidade de gerar AdventureWorks2014_FULL_file1.bak , AdventureWorks2014_FULL_file2.bak , AdventureWorks2014_FULL_file3.bak …..

Não vou entrar em detalhes com relação a questão do tamanho para não prolongar ainda mais, porém segue referencial da Microsoft para ajudar a entender melhor este contexto.

Entendendo bolhas de bloco, blobs de apêndice e bolhas de página – Armazenamento de Azure | Microsoft Docs
Fazendo backup de um VLDB para o Azure Blob Storage | Microsoft Docs
O OlaHallengren.com também comenta sobre este contexto, deixando tudo dinâmico nos seus scripts. Segue print.

Portanto vamos para a prática.

Caso você queira fazer o backup e dividir em vários arquivos é bem simples. Basta configurar na aba General e no botão Add (onde adicionamos o backup conforme o nome que optamos), vamos então acrescentar os arquivos e renomear conforme necessário para obter um bom entendimento. Basta ir adicionando um por um. Quando terminar, clique no Script para visualizar o código gerado.

Nosso script está todo OK, veja que o backup vai ser dividido em 3 arquivos. F5 para executar e pronto.

Vamos agora conferir como ficou. Veja o comparativo do tamanho dos arquivos.

Para finalizar nossa automatização do backup, faltou apenas criar schedule com o horário. Basta nas propriedades do Job clicar em Schedules e depois em New e então colocar qual será a frequência de execução e o horário. Clicar em OK para salvar e aguardar a execução.

Vamos validar?

Pronto, segue histórico de backups realizados. Temos o backup executado manualmente e depois a execução do backup feito via Job às 22h, conforme podemos ver o usuário do Agent.