Backup managed instance to storage account

David C 191 Reputation points
2021-08-04T17:13:08.33+00:00

I want to write a .bak of a managed instance database to a blob storage container in a storage account? My idea is to be able to grab that .bak to restore to on-prem DB in case necessary. The following article describes it:

https://learn.microsoft.com/en-us/sql/relational-databases/tutorial-sql-server-backup-and-restore-to-azure-blob-storage-service?view=sql-server-ver15&tabs=SSMS

I'm using SSMS on an on-prem server. We are connected to Azure via ExpressRoute. The on-prem traffic goes through a hub vnet, then to the vnet which contains the managed instance dedicated subnet. i can connect to the Managed instance database fine using SSMS. All the vnets in the path to the managed instance are peered.

Per the article, I'm able to connect to Azure and select the storage account per steps #1 thru #7. But SSMS crashes every time I try to select a blob container per step #8. BTW, this happens not only from a on-prem SSMS, but also when I try from a Azure VM and a bastion host.

I've tried a lot of guidance to ensure access to the storage account container. In the networking settings of the storage account, I give access to all of the subnets of the vnet. In the settings of the subnet which contains the managed instance, the subnet is delegated to "Microsoft SQL/managedinstances" and the service endpoint is "Microsoft storage".

Why is SSMS crashing every time I try to select the storage container, and what are the essential networking and Azure resource configuration to write a .bak to the container? Something to do with DNS zone, private endpoints, etc?

Thanks.

Azure SQL Database
{count} vote

Accepted answer
  1. Alberto Morillo 32,981 Reputation points MVP
    2021-08-04T22:35:56.48+00:00

    My suggestion is to create a bacpac and store it on an Azure storage account, that you can later download a restore on the on-premises instance. Please read here why you should not try to restore native Azure Managed Instance backups into your on-premises instance.

    You can use SSMS to export a database as bacpac to a storage account. Click here to know how.


0 additional answers

Sort by: Most helpful