Lesson Learned #82: Azure SQL Database Managed Instance supports only COPY_ONLY restoring a database backup

This post has been republished via RSS; it originally appeared at: MSDN Blogs.

Hello Team,

I would like to share an error message that we found restoring a backup database in Azure SQL Managed Instance. Our customer tried to restore the backup file using SQL Server Management Studio GUI from an existing database to another database.

So, our customer has in their Azure SQL Managed Instance a database called Database1, they run a backup following the instructions to have the backup:

CREATE CREDENTIAL [https://xxxx.blob.core.windows.net/xxxxx] 
, SECRET = 'sv=2018-03-28&ss=b&srt=sco&spxxxxxxxxxxxxxxxxxx';
TO URL = 'https://xxxx.blob.core.windows.net/xxxxx/backup.bak'

But when they try to restore using the following instructions they got the error:

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

Using SQL Server Management Studio GUI after specifying all the details, the script is generated is like this one:

USE [master]
BACKUP LOG [Database1] TO  URL = N'https://xxxxx.blob.core.windows.net/xxxxx/Database1_LogBackup_2019-03-22_21-21-35.bak' WITH NOFORMAT, NOINIT,  NAME = N'Database1_LogBackup_2019-03-22_21-21-35', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5
RESTORE DATABASE [Database2] FROM  URL = N'https://xxxxx.blob.core.windows.net/xxxx/backup.bak'

As you could see, SQL Server Management Studio GUI is trying to run a backup of the transaction log when there is not possible in Azure SQL Managed Instance and hence the error that our customer got.

The solution is to run the backup using TSQL command:

RESTORE DATABASE [Database2] FROM  URL = N'https://xxxx.blob.core.windows.net/xxxx/backup.bak'


Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.