5

Short Version:

I am trying to restore a backup from one SQL Managed Instance to another SQL Managed Instance. These are in different subscriptions. Using Example 3 from here I can at least get it to the point where I see the restore starting in sys.dm_operation_status, but the error message is "Subscription [target] does not contain [source SQL MI]".

Questions: How can I migrate the database from one SQL Managed Instance to another (in a different subscription)? My current workaround is to do copy_only to a blob storage and restore that way.

Once we enable TDE (in-progress) we won't be able to do copy_only backups to blob. What then?

Long Version:

Fairly recently our company made the jump to running our production workload in Azure on SQL Managed Instances (and other PaaS/IaaS) and this went fairly well. We had a large enough window identified that we were able to do the initial load with a full backup, although I did familiarize myself with the Database Migration Services (DMS).

But now we are expanding out our dev/test/qa environments and of course we now want to restore from production to these environments. For isolation purposes, each is getting it's own subscription.

These commands work, in the sense that I can at least see the restore operation start in sys.dm_operation_status but it fails with an error. The error is "Subscription [target] does not contain [source SQL MI]" and leads me to believe that cross subscription restores won't work for us (although cross region would).

DMS does not appear to support the scenario of using Database Migration Services as a source.

Select-AzSubscription -Subscription $SourceSubscriptionID

$GeoBackup = Get-AzSqlInstanceDatabaseGeoBackup -ResourceGroupName $SourceResourceGroupName `
     -InstanceName $SourceSQLMI `
     -Name $SourceDatabaseName

Select-AzSubscription -Subscription $TargetSubscriptionID

Restore-AzSqlInstanceDatabase -FromGeoBackup `
    -GeoBackupObject $GeoBackup `
    -TargetInstanceDatabaseName $TargetDatabaseName `
    -TargetInstanceName $TargetSQLMI `
    -TargetResourceGroupName $TargetResourceGroupName

Any advice, ideas? I'm willing to use preview versions of the various Azure PowerShell if necessary although I've not found anything to indicate that it's different there.

POSSIBLE WORKAROUND

I did get a link from one of our Microsoft contacts that seemed to indicate that you can stand up a managed instance in the same subscription, restore to it and then move the managed instance to another subscription. I have NOT attempted this so I don't know. The link they provided was here although I have my doubts about it's effectiveness.

4 Answers 4

6

copy only backups are currently the only solution. Cross subscription pitr and geo restore don't work because currently we cannot verify that initiator has all required permissions both on source and target subscriptions.

2
  • I'm marking this as the answer as it confirmed what I had already found. I did see a work-around posted (see my updated question) but I haven't directly attempted it, the copy to url method is what we are doing for now with a caution flag in our enable TDE project. Sep 9, 2019 at 12:29
  • @jovanpop - is this answer (copy to storage account, restore from storage account) still accurate, as it appears that as of Jan 2023 it's -possible- for PITR restores across subscription, but only from the Azure Portal rather than from PowerShell learn.microsoft.com/en-us/azure/azure-sql/managed-instance/… Frankly - I'm hoping that it's possible with PowerShell, though haven't been able to find PowerShell that doesn't error with an obscure error message of "Long running operation failed with status 'Failed'"...
    – David Ford
    Jan 24, 2023 at 11:03
7

Cross-subscription PITR restore for the same region and same Tenant ID (e.g.same customer/company only) is available as a limited preview for EA, CSP or PayGo subscriptions only at this time (April 2020). You would need to login into Azure portal (through EA/CSP/PayGo subscription) where you intend to use this and create a support ticket, selecting support option "Automated backups or point in time restore". In a few days you could get approved for the limited preview. This solution also supports TDE encrypted backups for Azure managed keys. For BYOK keys this will work only if customer has the same key on both source and destination MI. Hope this helps.

1
  • I wanted to take the time to thank you for your update. A while ago we were able to get onto the pre-view feature and it works as advertised. Still limited in our view as far as same region restrictions, but it does work. We did implement TDE recently and while I haven't tested a restore since, I have no reason to believe that it won't work. thank you. Aug 10, 2020 at 20:16
5

To add to Jovanpop's answer, you should still be able to take copy_only backups with TDE turned on if you are using AKV integration i.e. BYOK TDE, as long as your destination instance in other subscription has access to the same encryption key used on source instance.

0
1

Bradley Ball has a really good walk-through of the steps required to restore a Managed Instance database between 2 subscriptions in Cross Subscription Prod Refresh on SQL Managed Instance using TDE and Azure Key Vault on the Microsoft Data Architecture blog.

The post is long, with many details and screenshots but the outline is:

  1. Create a TDE Key in the Development Azure Key Vault
  2. Backup the Key
  3. Create a new “2-b-moved” Azure Key Vault
  4. Restore the Key to the new “2-b-moved” Azure Key Vault
  5. Migrate the new “2-b-moved” Azure Key Vault from the development subscription to the production subscription
  6. Backup the key from the new “2-b-moved” Azure Key Vault now in the production subscription
  7. Restore the Key to the production Azure Key Vault
  8. Associate both SQL Managed Instances, Production & Development, with the Key’s in their respective Azure Key Vaults
  9. Create a T-SQL Credential on each Azure SQL Managed Instances to an Azure Blob Storage account
  10. Backup the TDE database from the production SQL Managed Instance and restore it to the development SQL Managed Instance
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.