- 6 minutes to read

Upload and Restore SQL Managed Instance .bak from Azure Blob Storage

This technical guide provides a compact, repeatable flow for two operations:

  • Upload a local SQL Server backup file (.bak) to Azure Blob Storage
  • Restore the database from Blob Storage to Azure SQL Managed Instance

The scripts in this article are designed for operations teams that need predictable execution, explicit login steps, and minimal manual SSMS work.

What this article includes

  • Azure login and subscription selection
  • Upload script for local .bak to Blob Storage
  • Restore script for Azure SQL Managed Instance using URL + SAS credential
  • Validation and progress checks
  • Condensed mapping to Microsoft Quickstart

Prerequisites

  • PowerShell 7
  • Network connectivity to Azure SQL Managed Instance endpoint
  • Permissions:
    • Storage Blob Data Contributor on the target storage account (for upload)
    • SQL permissions on managed instance to create credential and restore database
  • Modules:
    • Az.Accounts
    • Az.Storage
    • SqlServer

Install required modules:

Install-Module Az.Accounts -Scope CurrentUser -Repository PSGallery -Force
Install-Module Az.Storage -Scope CurrentUser -Repository PSGallery -Force
Install-Module SqlServer -Scope CurrentUser -Repository PSGallery -Force

Step 1 - Login to Azure 

Connect-AzAccount -AuthScope Storage
Set-AzContext -Subscription "<subscription-id-or-name>"
Get-AzContext | Format-List Account, Subscription, Tenant, Environment

If your environment blocks popup-based auth:

Connect-AzAccount -AuthScope Storage -UseDeviceAuthentication
Set-AzContext -Subscription "<subscription-id-or-name>"

Step 2 - Upload local .bak to Blob Storage

Use the embedded script below.

#Requires -Version 7.0

[CmdletBinding()]
param(
  [Parameter(Mandatory = $true)]
  [string]$Subscription,

  [Parameter(Mandatory = $true)]
  [string]$StorageAccountName,

  [Parameter(Mandatory = $true)]
  [string]$ContainerName,

  [Parameter(Mandatory = $true)]
  [string]$LocalBakPath,

  [string]$BlobName,
  [int]$SasExpiryHours = 24,
  [switch]$CreateContainerIfMissing
)

Set-StrictMode -Version Latest
$ErrorActionPreference = "Stop"

Import-Module Az.Accounts -ErrorAction Stop
Import-Module Az.Storage -ErrorAction Stop

if (-not (Test-Path -LiteralPath $LocalBakPath -PathType Leaf)) {
  throw "Local .bak file not found: $LocalBakPath"
}

if ([string]::IsNullOrWhiteSpace($BlobName)) {
  $BlobName = [System.IO.Path]::GetFileName($LocalBakPath)
}

Connect-AzAccount -AuthScope Storage | Out-Null
Set-AzContext -Subscription $Subscription | Out-Null

$ctx = New-AzStorageContext -StorageAccountName $StorageAccountName -UseConnectedAccount

$container = Get-AzStorageContainer -Name $ContainerName -Context $ctx -ErrorAction SilentlyContinue
if (-not $container) {
  if (-not $CreateContainerIfMissing) {
    throw "Container '$ContainerName' does not exist. Use -CreateContainerIfMissing to create it."
  }

  New-AzStorageContainer -Name $ContainerName -Context $ctx -Permission Off | Out-Null
}

Write-Host "Uploading '$LocalBakPath' to '$ContainerName/$BlobName'..."

$uploadParams = @{
  File      = $LocalBakPath
  Container = $ContainerName
  Blob      = $BlobName
  Context   = $ctx
  Force     = $true
}

Set-AzStorageBlobContent @uploadParams | Out-Null

$expiry = (Get-Date).ToUniversalTime().AddHours($SasExpiryHours)
$containerSas = New-AzStorageContainerSASToken -Name $ContainerName -Context $ctx -Permission rl -ExpiryTime $expiry
$sasToken = $containerSas.TrimStart('?')

$blobUrl = "https://$StorageAccountName.blob.core.windows.net/$ContainerName/$BlobName"
$containerUrl = "https://$StorageAccountName.blob.core.windows.net/$ContainerName"

Write-Host "Upload completed."
Write-Host "BlobUrl: $blobUrl"
Write-Host "ContainerUrl: $containerUrl"
Write-Host "SasToken (no leading ?): $sasToken"
Write-Host "SasExpiryUtc: $($expiry.ToString('u'))"

Example:

# Save the embedded script to a local .ps1 file, then run it:
# pwsh .\Upload-BakToBlobStorage.ps1 ...

Expected output includes:

  • Blob URL
  • Container URL
  • SAS token (without leading ?)

Save these values for restore.

Step 3 - Restore the database from Blob to Managed Instance

Use the embedded script below.

#Requires -Version 7.0

[CmdletBinding()]
param(
  [Parameter(Mandatory = $true)]
  [string]$ManagedInstanceFqdn,

  [Parameter(Mandatory = $true)]
  [string]$DatabaseName,

  [Parameter(Mandatory = $true)]
  [string]$BlobUrl,

  [Parameter(Mandatory = $true)]
  [string]$SasToken,

  [Parameter(Mandatory = $true)]
  [string]$SqlUser,

  [Parameter(Mandatory = $true)]
  [string]$SqlPassword,

  [int]$QueryTimeoutSeconds = 0
)

Set-StrictMode -Version Latest
$ErrorActionPreference = "Stop"

Import-Module SqlServer -ErrorAction Stop

if ([string]::IsNullOrWhiteSpace($BlobUrl) -or -not $BlobUrl.StartsWith("https://")) {
  throw "BlobUrl must be a valid https URL."
}

$sasTokenClean = $SasToken.TrimStart('?')

$lastSlash = $BlobUrl.LastIndexOf('/')
if ($lastSlash -lt 9) {
  throw "BlobUrl must include container path and blob name."
}

$credentialUrl = $BlobUrl.Substring(0, $lastSlash)

$escapedCredentialUrl = $credentialUrl.Replace("'", "''")
$escapedSasToken = $sasTokenClean.Replace("'", "''")
$escapedBlobUrl = $BlobUrl.Replace("'", "''")
$escapedDatabaseName = $DatabaseName.Replace("]", "]]")

$sql = @"
IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = N'$escapedCredentialUrl')
BEGIN
  DROP CREDENTIAL [$escapedCredentialUrl];
END;

CREATE CREDENTIAL [$escapedCredentialUrl]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '$escapedSasToken';

RESTORE FILELISTONLY
FROM URL = '$escapedBlobUrl';

RESTORE DATABASE [$escapedDatabaseName]
FROM URL = '$escapedBlobUrl';
"@

Write-Host "Starting restore for database '$DatabaseName' from '$BlobUrl'..."

$invokeParams = @{
  ServerInstance = $ManagedInstanceFqdn
  Database       = "master"
  Username       = $SqlUser
  Password       = $SqlPassword
  Query          = $sql
  QueryTimeout   = $QueryTimeoutSeconds
}

Invoke-Sqlcmd @invokeParams | Out-Null

Write-Host "Restore command submitted."
Write-Host "Use the following query to monitor progress:"
Write-Host "SELECT session_id AS SPID, command, start_time, percent_complete FROM sys.dm_exec_requests WHERE command IN ('RESTORE DATABASE', 'BACKUP DATABASE');"

Example:

# Save the embedded script to a local .ps1 file, then run it:
# pwsh .\Restore-MiDatabaseFromBlob.ps1 ...

The script performs:

  1. CREATE CREDENTIAL for the Blob container URL
  2. RESTORE FILELISTONLY from the Blob URL
  3. RESTORE DATABASE from the Blob URL
  4. Progress query against sys.dm_exec_requests

Alternative Solution - T-SQL only (when .bak is already in Blob)

Use this path when you have already uploaded the backup file and only need restore operations in SSMS.

-- 1) Set values
DECLARE @ContainerUrl nvarchar(4000) = N'https://<storage>.blob.core.windows.net/<container>';
DECLARE @BlobUrl      nvarchar(4000) = N'https://<storage>.blob.core.windows.net/<container>/<file>.bak';
DECLARE @SasToken     nvarchar(max)  = N'<sas-token-without-leading-question-mark>';

-- 2) Recreate credential
IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = @ContainerUrl)
BEGIN
  DECLARE @drop nvarchar(max) = N'DROP CREDENTIAL [' + @ContainerUrl + N'];';
  EXEC(@drop);
END;

DECLARE @create nvarchar(max) =
N'CREATE CREDENTIAL [' + @ContainerUrl + N']
WITH IDENTITY = ''SHARED ACCESS SIGNATURE'',
SECRET = ''' + REPLACE(@SasToken, '''', '''''') + N''';';
EXEC(@create);

-- 3) Validate access to the backup
DECLARE @filelist nvarchar(max) =
N'RESTORE FILELISTONLY FROM URL = ''' + @BlobUrl + N''';';
EXEC(@filelist);

-- 4) Restore database
DECLARE @restore nvarchar(max) =
N'RESTORE DATABASE [NQA_Restored]
FROM URL = ''' + @BlobUrl + N''';';
EXEC(@restore);

Tip

On Azure SQL Managed Instance, do not use WITH STATS in this restore statement. Use DMV queries to track progress.

Monitor restore progress 

SELECT session_id AS SPID,
       command,
       start_time,
       percent_complete,
       DATEADD(second, estimated_completion_time / 1000, GETDATE()) AS estimated_completion_time
FROM sys.dm_exec_requests
WHERE command IN ('RESTORE DATABASE', 'BACKUP DATABASE');

SELECT operation,
       major_resource_id,
       state,
       state_desc,
       percent_complete,
       error_code,
       error_desc,
       start_time,
       last_modify_time
FROM sys.dm_operation_status
WHERE major_resource_id = 'NQA_Restored'
ORDER BY start_time DESC;

This article condenses the SSMS-focused quickstart into script-first operations.

Key parity points with Microsoft guidance:

  • Credential name must match the container path and use https
  • IDENTITY must be SHARED ACCESS SIGNATURE
  • SAS secret must not include leading ?
  • Restore is asynchronous and can be monitored via DMVs

Common errors

StorageOAuthEndpointResourceId authentication failed

Run login again with storage auth scope:

Connect-AzAccount -AuthScope Storage
Set-AzContext -Subscription "<subscription-id-or-name>"

Error 3201 or Operating system error 86 during restore

Usually indicates credential/SAS mismatch. Recreate credential with correct container URL and SAS token without leading ?.

Msg 41901: option is not supported for this statement

This is typically caused by unsupported options (for example WITH STATS) on Azure SQL Managed Instance restore syntax. Remove the unsupported option and run plain RESTORE DATABASE ... FROM URL ...;.

No progress visible after starting restore

Restore is asynchronous. Keep monitoring with the DMV query in this article.