Unable to open the “Properties” window for databases on the Managed Instance using SSMS. Getting error message.
Subquery returned more than one value. This isn’t permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression. (.NET SqlClient Data Provider)
If user-initiated copy only backups were performed in this managed instance, it’s possible that this is caused a known issue in those databases that generates incorrect data inserted in the msdb.dbo.backupset table. More information on that issue can be found here: Release notes for (SSMS) – SQL Server Management Studio (SSMS) | Microsoft Learn | https://learn.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-ver16#known-issues-1902
There were copy only backups previously executed in the managed instance that were creating duplicates in the backup history by running the query below.
To resolve the issue, incorrect backup history was removed by following instructions from the link above.
After execution of above script, we was able to successfully open the property page of databases using SSMS.