Unable to Restore SQL MI Database by SSMS

Question

Hi team,

I backed up an Azure Arc-enabled SQL Managed Instance database using SSMS to an Azure blob. However, when I try to restore it, I encounter the following error:

"Restore of database 'restorebook' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

ADDITIONAL INFORMATION: Microsoft.Data.SqlClient.SqlError: RESTORE cannot operate on database 'restorebook' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group. (Microsoft.SqlServer.Smo)"

According to the error message, the database is part of an availability group, but according to SC (Service Center), it is not.

Please assist.

Answer 1

Hello Demo,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Problem

I understand that you are unable to restore SQL MI Database by SSMS.

Solution

The error message you're encountering typically indicates that the database you are trying to restore is either configured for database mirroring or has been part of an availability group at some point. Use the SQL statement below to perform the same restore, however check through the comments to see the steps available to solve the issue.

-- 1. Check Availability Group membership
SELECT d.name, d.state_desc, ag.name as AvailabilityGroup
FROM sys.databases d
LEFT JOIN sys.availability_databases_cluster agdb ON d.database_id = agdb.database_id
LEFT JOIN sys.availability_groups ag ON agdb.group_id = ag.group_id
WHERE d.name = 'restorebook';
-- 2. If part of an Availability Group, remove it
IF EXISTS (SELECT * FROM sys.availability_databases_cluster WHERE database_id = DB_ID('restorebook'))
BEGIN
    ALTER DATABASE restorebook SET HADR OFF;
END
-- 3. Check Database Mirroring
SELECT name, mirroring_state_desc
FROM sys.database_mirroring
WHERE name = 'restorebook';
-- 4. If mirroring is configured, disable it
IF EXISTS (SELECT * FROM sys.database_mirroring WHERE name = 'restorebook' AND mirroring_state IS NOT NULL)
BEGIN
    ALTER DATABASE restorebook SET PARTNER OFF;
END
-- 5. Drop the existing database if it exists
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'restorebook')
BEGIN
    DROP DATABASE restorebook;
END
-- 6. Restore the database
RESTORE DATABASE restorebook 
FROM URL = 'your_backup_file_url'
WITH REPLACE, STATS = 10;

In addition, to the above solution.

• Ensure that you have the necessary permissions to perform these operations on the SQL Managed Instance.
• Verify the configuration of your Azure Arc-enabled SQL Managed Instance to ensure it supports the operations you are trying to perform.

Accept Answer

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful ** so that others in the community facing similar issues can easily find the solution.

Best Regards,

Sina Salam

Answer 2

Hello @Sina Salam , Thanks for your reply.
Is there any workaround for restoring databases supported by Azure Arc-enabled SQL Managed Instance, since SSMS does not directly support restore? I've tried restoring using SSMS, Azure Data Studio, SQL Query, and PowerShell, but none have been successful. I also checked Azure Backup, but it only offers options for Azure, not for Azure Arc.

Answer 3

Hi Microsoft Team,
Can someone help me here?