Share via

SQL Server Backup in Azure VMs

Onetrowon Emmanuel 0 Reputation points
2024-07-05T16:58:17.4633333+00:00

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?

Azure Backup
Azure Backup
An Azure backup service that provides built-in management at scale.
1,182 questions
Azure Virtual Machines
Azure Virtual Machines
An Azure service that is used to provision Windows and Linux virtual machines.
7,515 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,269 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vahid Ghafarpour 20,480 Reputation points
    2024-07-05T17:21:44.39+00:00

    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 **

  2. Onetrowon Emmanuel 0 Reputation points
    2024-07-08T15:41:12.9533333+00:00