Restore SQL DB from Managed Instance to Local

Question

I just migrated our databases from an Azure VM to Managed Instance. I am using TDE on the MI with a customer managed key in Azure Key Vault. I am making a COPY ONLY backup nightly to blob storage so I can restore locally if needed. How do i restore these databases? I know I'm missing at least one step but I am getting this error:

Cannot find server asymmetric key with thumbprint 'X'.
RESTORE HEADERONLY is terminating abnormally.

Can someone please point me to a guide or tell me the steps for this?

Thanks
Chris

Answer 1

"Officially backup/restore from Managed Instance to on-premises SQL Server don’t work because Managed Instance is always the latest version of database engine and you cannot restore the backups from the higher version to earlier version."
Source: Migrate from Azure SQL Database using BCP

The author of the article is an Azure SQL Database Managed Instance manager. He suggest two other methods to take your database to your on-premises environment: one using bcp utility and the other using transactional replication.

You can still try to use the native RESTORE statement but you need to turn off the encryption prior to take the backup as explained in this article, and have the on-premises instance with SQL Server 2019 and the latest cumulative update (CU9 at this time).

Answer 2

I ended up writing a powershell script to export a bacpac of each database to my local pc using sqlpackage then upload to Azure blob storage which is run on a schedule. It's not a perfect solution but I need to have access to the data for development/support. I really like the managed instance so far with the exception of this. There should be a more integrated way to backup and restore MI databases to local or at least to export a bacpac directly to Azure blob.

Here's the script. I'm not very experienced with powershell but it works...

#SQLPackage - your location may be different - you can find it using this in cmd: where /R c:\ SqlPackage.exe
$sqlPackageFileName = "c:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\sqlpackage.exe"

#Database connection
$targetServerName = "TargetServerSQLManagedInstance"
$username = "username"
$password = "password"

#Storage Connection
$subscriptionId = "SubscriptionId"
$storageAccountRG = "StorageAccountRG"
$storageAccountName = "StorageAccountName"
$storageContainerName = "StorageContainerName"
$storageAccountKey = "StorageAccountKey"

# Add all databases by name to the array
$databases = @("Database1", "Database2")

foreach ($database in $databases) {
    $Now = Get-Date -Format "MMddyyyy_hhmmss"
    $blob = $database + "_" + $Now + ".bacpac"
    $filename = "C:\Temp\" + $blob

    & $sqlPackageFileName /Action:Export /ssn:$targetServerName /sdn:$database /su:$username /sp:$password /tf:$filename /p:Storage=File

    # Set AzStorageContext
    $destinationContext = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageAccountKey

    # Upload File 
    Set-AzStorageBlobContent -File $filename -Container $storageContainerName -Blob $blob -Context $destinationContext -StandardBlobTier Hot 

    #Remove file from local
    Remove-Item $filename
}