question

ChrisJohnson-9233 avatar image
1 Vote"
ChrisJohnson-9233 asked ChrisJohnson-9233 answered

Restore SQL DB from Managed Instance to Local

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

azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered ChrisJohnson-9233 commented

"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).



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Alberto. I appreciate the information. I used a dacpac yesterday which is a bit slow. I'll check out the BCP option.

1 Vote 1 ·
ChrisJohnson-9233 avatar image
1 Vote"
ChrisJohnson-9233 answered

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
 }

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.