4

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

jarlh
  • 42,561
  • 8
  • 45
  • 63
Gaurav Modi
  • 51
  • 1
  • 2
  • 3

3 Answers3

2

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

Alex Ayscough
  • 101
  • 6
1

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:

enter image description here

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

  1. Create credential

  2. Back up database

Hope this helps

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • 1
    It gives me a error "Msg 41922, Level 16, State 1, Line 46 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 46 BACKUP DATABASE is terminating abnormally. " – Gaurav Modi Apr 02 '20 at 12:41
  • Indeed! there is no easy way out here: encryption must be disabled (see https://stackoverflow.com/a/54325238/3518693) – NiceGuyAlberto Apr 20 '22 at 06:35
1

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.

Shashikant Shakya
  • 114
  • 5