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]
WITH IDENTITY='SHARED ACCESS SIGNATURE'
, SECRET = 'sv=2018-03-28&ss=b&srt=sco&spxxxxxxxxxxxxxxxxxx';
BACKUP DATABASE Database1
TO URL = 'https://xxxx.blob.core.windows.net/xxxxx/backup.bak'
WITH COPY_ONLY
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.
Why?
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'
Enjoy!