-> Azure SQL Managed instance has in-built database backups called Automated backups. It is not required for users to schedule regular backups manually. Refer this article for more details.

-> Performing a manual database backup sometime becomes mandatory in Managed instance.

-> Lets first create a storage account so that we can perform a manual backup of a database in Managed instance.

-> Create a storage account using above details. Once storage account is created, add a container into that storage account.

-> Backup database from SQL Server management studio using below method,

-> Signin to the azure portal.

-> Click “OK” after the credentials are created.

-> Script out the backup script by clicking on Script and selecting “Script Action to New Query Window”.

-> Notice “COPY_ONLY” included in the script. Lets try removing COPY_ONLY and try a backup. it fails with below error,

Msg 41904, Level 16, State 1, Line 3
BACKUP DATABASE failed. SQL Database Managed Instance supports only COPY_ONLY full database backups which are initiated by user.

-> Error message clearly advises us to take a backup with COPY_ONLY option. Lets try performing the backup with COPY_ONLY and it fails with below error this time,

Msg 41922, Level 16, State 1, Line 3
The backup operation for a database with service-managed transparent data encryption is not supported on SQL Database Managed Instance.
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.

-> This error is related to Transparent Data Encryption (TDE) that is enabled by default in Managed instance using Service-Managed key. TDE with Service-Managed key does not allow copy-only backups. Lets configure TDE to use a Customer-Managed key and then perform a backup.

PLEASE NOTE – I am performing this task on a TEST subscription with no active application connecting to the database. If you are planning this on a Production database, you need to plan and test things well in advance and then perform it on a production instance, as this will have major impact while implementation.

-> I will use Customer-Managed key and place the key in an Azure key vault,

-> Create the Azure key vault using above details.

-> Click “Save” and you are ready to go.

-> Lets try performing a backup with COPY_ONLY option on the database in Managed instance. This time it works fine.

-> We are able to see the file in the storage account also.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.