How to take backup of azure SQL-Managed Instance backup to azure blob

Question

I am having a SQL-Managed Instance database now I wanted to take backup in .bak format to blob storage. The current Command I am using is

WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
, SECRET = 'Pasted my sas token generated from azure portal blob storage'
go

BACKUP DATABASE [DB_Name]
TO URL = 'blob url/cointainer name/testing.bak'with checksum;

But by this I am getting a error: "BACKUP DATABASE failed. SQL Database Managed Instance supports only COPY_ONLY full database backups which are initiated by user."

I have also tried to give "COPY_ONLY" instead of checksum but then again I am facing a error: "Msg 41922, Level 16, State 1, Line 6 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 6 BACKUP DATABASE is terminating abnormally. "

Note: I have a database of approx size 800GB

user_2190674 · Answer

To prevent the original error message and you are comfortable with the increased security risks you can remove encryption:

Alter database [database_name] set encryption Off

use [database_name]

DROP DATABASE ENCRYPTION KEY

user_4329978 · Answer

Error is related to service managed TDE encryption since all database by default encrypted and service managed TDE does not allow to take copy_only backups. You need to either disable service managed TDE or Enable TDE with customer managed keys to take backups. Since your database size is 800 GB and if BackupSize > 200 GB then split your backups to multiple files. This is a limitation with blockblob.

user_10549281 · Answer

The backup command should be:

USE [master]

BACKUP DATABASE [SQLTestDB] 
TO  URL = N'https://msftutorialstorage.blob.core.windows.net/sql-backup/sqltestdb_backup_2020_01_01_000001.bak' 
WITH  COPY_ONLY, CHECKSUM
GO

You could follow this Azure tutorial: Quickstart: SQL backup and restore to Azure Blob storage service:

It will help you backup the database(.bak) to Blob Storage step by step:

Hope this helps

How to take backup of azure SQL-Managed Instance backup to azure blob

3 Answers