Restoring Backups from Azure Blob with dbatools

Recently I needed to write a PowerShell script that could build a backup set from a collection of backups stored in Azure Blob Storage without any backup history available from MSDB. And as with all things SQL Server and PowerShell related I went straight to dbatools.io to see if Restore-DbaDatabase was up to the task…and of course, it is…let’s talk about how I solved this challenge.

When restoring from Azure Blob, the main challenge you have is accessing the blobs and building a backup set. For this process, you’ll need access to the Storage Account via PowerShell and you’ll have to have define a Credential on your SQL Instance that has access to the Storage Account. Here’s the code I used to connect to my Storage Account in Azure.

Connect-AzAccount -Tenant ‘867-5309'

$StorageAccount = Get-AzStorageAccount -ResourceGroupName 'MyResourceGroup' -Name 'testdbaen'

$BlobUrl = 'https://testdbaen.blob.core.windows.net/testdb';

Building a BackupSet

The first step is to get a collection of backups we want to build our backup set from. In my scenario, I know that we have weekly fulls, so I can limit the search space by getting only backups within the last seven days from the total set of backups in the Storage Account. In this code section, we use Get-AzStorageBlob to get the collection of Blobs (backups) with the parameters Context, which is used to define our the location of and log in to the Storage Account where the backups are stored, and Container which is the container the blobs are stored in.

$DaysBack = 7
$DateBack = (Get-Date).ToUniversalTime().AddDays(-$DaysBack)
$BlobsToRestoreFrom = Get-AzStorageBlob -Container 'testdb' -Context $StorageAccount.Context | Where-Object { $_.Name -like '*.bak' }

 Once we have a collection of Blobs to work with we need to build a second set the URI and Blob name in a format that Get-DbaBackupInformation likes

#Build a list of backup files available in URI/Name format
$FilesToRestore = @()
foreach ( $Blob in $BlobsToRestoreFrom ){
 $FilesToRestore += "$BlobUrl/$($Blob.Name)"
}

With that set of properly formatted file references, let’s hand that information into Get-DbaBackupInformation to build a set of backup history from the files. In this code I’m referencing a server I want to restore to, SqlInstance, a SqlCredential used to log into that server, filtering the databases from the backup set to the databases I want to restore with DatabaseName and defining the Path to the backups. That’s the set we just build and stored in $FilesToRestore in the code above. Store the output of Get-DbaBackupInformation in a variable for use later.

$BackupHistory = Get-DbaBackupInformation `
    -SqlInstance $RestoreServer -SqlCredential $RestoreCredential `
    -DatabaseName @(‘DB1’,'DB2') `
    -Path $FilesToRestore

Restoring using Restore-DbaDatabase

 Once we have our restore history we can pipe that into Restore-DbaDatabase and off our Instance goes restoring the databases. 

$BackupHistory | Restore-DbaDatabase -SqlInstance $SqlInstance -OutputScriptOnly -AzureCredential $BlobUrl

Huge shout out to Shawn Melton and Stuart Moore for their help on this and of course to the whole dbatools.io team for everything they do!