Executar backup e restore de bancos de dados MS SQL com o PowerShell

Fazer backup de banco de dados MS SQL com PowerShell é rápido e eficiente, permitindo a automação simples e repetitiva. Com apenas alguns comandos, é possível agendar e gerenciar backups de forma personalizada. Além disso, a integração com o SQL Server Management Objects (SMO) facilita o controle total do processo. Aqui faremos um POST rápido sobre esse assunto, seremos breves e abordaremos de uma maneira também bem simples com o objetivo de agregar o máximo da forma mais rápida.

Sugerimos que as etapas descritas aqui sejam executadas no PowerShell como administrador, a fim de evitar problemas de execução por falta de permissão

Entendendo o que é um DUMP de um banco de dados

Ao falar de backup de banco de dados é importante que você saiba o que é um dump, pois a maioria dos backups envolve o processe de dump da base (ou para a base, no caso de uma recuperação). Um dump de banco de dados é o processo de exportar a estrutura e dos dados de um banco de dados para um arquivo, nele estão todas as instruções necessárias para recriar os dados e a estrutura do banco em outro sistema, facilitando a recuperação e/ou migração de informações.

Ou seja, quando falamos de backup em arquivo, estamos nos referindo ao processo de gerar um dump, onde os dados são exportados de maneira legível para serem armazenados com segurança.

Método 1 – Sem o módulo do SQL para o PowerShell

Backup

No fórum do SQL ServerCentral a Julia postou como executar comandos no SQL com o PowerShell usando o objeto Microsoft.SqlServer.Management. Segue um exemplo de código para Backup com o PowerShell (altere as partes em cinza conforme sua necessidade):

#--------------- Definindo varáveis ---------------
$serverInstance = "servidor/instancia"
$databaseName = "NomeDaBase"
$date = Get-Date -Format "yyyyMMdd"
$backupPath = "C:\Backup$databaseName`_$date.bak"
#--------------- Conectando ao SQL, definindo a Database e o backup ---------------
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverInstance)
$database = $server.Databases[$databaseName]
#$TapeDevice = New-Object Microsoft.Sqlserver.Management.Smo.BackupDeviceItem("\\.\tape0", "Tape")
$backupdevice = New-Object New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem($backupPath, "File")
#--------------- Executando o backup ---------------
$backup = New-Object Microsoft.SqlServer.Management.Smo.Backup
$backup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$backup.Database = $database.Name
$backup.Devices.Add($backupdevice)
$backup.SqlBackup($server)

Restore

O código do restore será bem parecido, mas utilizando Microsoft.SqlServer.Management.Smo.RestoreDeviceItem no lugar de Microsoft.SqlServer.Management.Smo.BackupDeviceItem. Segue um exemplo de código para Restore com o PowerShell (altere as partes em cinza conforme sua necessidade):

#--------------- Definindo varáveis ---------------
$serverInstance = "NomeDoServidor/NomeDaInstancia"
$databaseName = "NomeDaBase"
$backupPath = "C:\Backup\NomeDoArquivo.bak"
#--------------- Conectando ao SQL, definindo a Database e o backup ---------------
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverInstance)
$database = $server.Databases[$databaseName]
$restoreDevice = New-Object Microsoft.SqlServer.Management.Smo.RestoreDeviceItem($backupPath, "File")
#--------------- Executando o backup ---------------
$restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
$restore.Database = $database.Name
$restore.Devices.Add($restoreDevice)
$restore.NoRecovery = $false
$restore.SqlBackup($server)

Método 2 – Com o módulo do SQL para o PowerShell

Com o módulo do SQL para o PowerShell passamos a ter muitas possibilidades, entre elas:

• Fazer backup da base ou do log transacional;
• Fazer backup diferencial;
• Fazer backup em uma unidade de fita (tape);
• Fazer backup no serviço Azure Blog Storage;
• Encriptar o backup.

Apesar de alguma das opções acima talvez serem possíveis no outro método, demandaria um tempo considerável para entender e adaptar para funcionar (por exemplo, fazer o backup em tape). Utilizando o módulo a execução se torna mais simples.

Importanto o módulo

Para instalar o módulo do SQL para o PowerShell, execute como administrador o comando no servidor SQL:

Import-Module SQLPS

Depois de instalar, verifique se consegue executar os comandos Backup-SqlDatabase e Restore-SqlDatabase com sucesso.

Backup

Conforme descrito sobre o comando Backup-SqlDatabase, no site da Microsoft, alguns exemplos para o backup são:

Para fazer o backup de uma base de dados (database):

Backup-SqlDatabase -ServerInstance "NomeDoServidor\NomeDaInstancia" -Database "NomeDaBase"

Para fazer o backup do log (utilizando -BackupAction):

Backup-SqlDatabase -ServerInstance "NomeDoServidor\NomeDaInstancia" -Database "NomeDaBase" -BackupAction Log

Para fazer o backup em uma pasta de rede (utilizando -BackupFile):

Backup-SqlDatabase -ServerInstance "NomeDoServidor\NomeDaInstancia" -Database "NomeDaBase" -BackupFile "\\ServidorDeArquivos\PastaCompartilhada\NomeDaBase.bak"

Para fazer o backup de todas as bases de uma instância:

Get-ChildItem "SQLSERVER:\SQL\NomeDoServidor\NomeDaInstancia\Databases" | Backup-SqlDatabase

Para fazer o backup para uma unidade de fita (utilizando -BackupDevice):

$TapeDevice = New-Object Microsoft.Sqlserver.Management.Smo.BackupDeviceItem("\\.\tape0", "Tape")
Backup-SqlDatabase -ServerInstance "NomeDoServidor\NomeDaInstancia" -Database "NomeDaBase" -BackupDevice $TapeDevice

Para fazer o backup para o serviço Azure Blog Storage (utilizando -BackupContainer):

Backup-SqlDatabase -ServerInstance "NomeDoServidor\NomeDaInstancia" -Database "NomeDaBase" -BackupContainer "https://storageaccountname.blob.core.windows.net/Containername" -SqlCredential "SQLCredentialName"

Restore

Conforme descrito sobre o comando Restore-SqlDatabase, no site da Microsoft, alguns exemplos para o backup são:

Para restaurar o backup de um arquivo ou pasta de rede (utilizando -BackupFile):

Restore-SqlDatabase -ServerInstance "NomeDoServidor\NomeDaInstancia" -Database "NomeDaBase" -BackupFile "\\ServidorDeArquivos\PastaCompartilhada\NomeDaBase.bak"

Para restaurar o backup do log (utilizando -RestoreAction):

Restore-SqlDatabase -ServerInstance "NomeDoServidor\NomeDaInstancia" -Database "NomeDaBase" -BackupFile "\\ServidorDeArquivos\PastaCompartilhada\NomeDaBase.bak" -RestoreAction Log

Para restaurar e solicitar a credencial de acesso (utilizando -Credential):

Restore-SqlDatabase -ServerInstance "NomeDoServidor\NomeDaInstancia" -Database "NomeDaBase" -BackupFile "\\ServidorDeArquivos\PastaCompartilhada\NomeDaBase.bak" -Credential (Get-Credendial) 

Para restaurar o backup de uma unidade de fita (utilizando -BackupDevice):

$TapeDevice = New-Object Microsoft.Sqlserver.Management.Smo.BackupDeviceItem("\\.\tape0", "Tape")
Restore-SqlDatabase -ServerInstance "NomeDoServidor\NomeDaInstancia" -Database "NomeDaBase" -BackupDevice $TapeDevice

Para restaurar o backup do serviço Azure Blog Storage (utilizando -BackupFile):

Restore-SqlDatabase -ServerInstance "NomeDoServidor\NomeDaInstancia" -Database "NomeDaBase" -BackupFile "https://storageaccountname.blob.core.windows.net/Containername" -SqlCredential "SQLCredentialName"

Caso tenha interesse em ver mais opções de restauração (como data específica, restaurar em versões diferentes de banco de dados, entre outras), leia todos os argumentos/sintaxe que o comando permite, ou leia mais sobre o comando Restore-SqlDatabase, no site da Microsoft.

Fontes/Referências

https://learn.microsoft.com/en-us/powershell/module/sqlserver/backup-sqldatabase
https://learn.microsoft.com/en-us/powershell/module/sqlserver/restore-sqldatabase
https://paulosantanna.com/2022/10/25/script-powershell-que-verifica-database-mais-recente-em-um-diretorio-e-restaura-no-sql-server/
https://pt.wikipedia.org/wiki/Dump_de_banco_de_dados
https://sqlservercentral.com/forums/topic/backup-restore-sqldatabase-via-powershell-dont-work

Mais Informações

Esperamos ter ajudado e estamos à disposição para mais informações.

Se você tem interesse em algum assunto específico, tem alguma dúvida, precisa de ajuda, ou quer sugerir um post, entre em contato conosco pelo e-mail equipe@nvlan.com.br.