SQL Server Backup in Azure VMs

Question

The Azure backup for SQL VM doesn't allow the selection of multiple databases to be restored through the Azure portal. E.g. if I need to restore 10 databases which have already been backup up by Azure backup, I need to go through the restore process 10 times.

I guess multiple DBs can be selected to restore through PoweeShell. Could someone provide a Powershell script to achieve this task?

Answer 1

Thanks for posting your question in the Microsoft Q&A forum.

You need to create a SQL Credential on All Instances

# Load the sqlps module
import-module sqlps
# Set parameters
$sqlPath = "sqlserver:\sql\$($env:COMPUTERNAME)"
$storageAccount = "<myStorageAccount>"
$storageKey = "<myStorageAccessKey>"
$secureString = ConvertTo-SecureString $storageKey -AsPlainText -Force
$credentialName = "myCredential-$(Get-Random)"
Write-Host "Generate credential: " $credentialName
# CD to SQL Server and get instances
Set-Location $sqlPath
Get-ChildItem | ForEach-Object {
    $instance = $_.Name
    $credential = New-Object System.Management.Automation.PSCredential ("<storageAccountName>", $secureString)
    New-SqlCredential -Name $credentialName -Identity $credential -Instance $instance
}

And next Restore Databases

Restore-SqlDatabase -ServerInstance Server/InstanceName -Database Database1 -BackupFile "PathToFile1.bak" -ReplaceDatabase
Restore-SqlDatabase -ServerInstance Server/InstanceName -Database Database2 -BackupFile "PathToFile2.bak" -ReplaceDatabase
# Repeat for other databases

You can use following article for more options

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-multiple-databases-to-azure-blob-storage-powershell?view=sql-server-ver16

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **

Answer 2

Manager to do it with CLI.

https://learn.microsoft.com/en-us/azure/backup/backup-azure-sql-restore-cli