Unable to open the “Properties” window for databases on the Managed Instance

Issue Definition

Unable to open the “Properties” window for databases on the Managed Instance using SSMS. Getting error message.

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)

Root Cause

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: Rele a se 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

Solution

There were copy only backups previously executed in the managed instance that were creating duplicates in the backup history by running the query below.

CREATE TABLE #tempbackup (database_name nvarchar(128), [type] char(1), backup_finish_date datetime)

INSERT INTO #tempbackup
SELECT database_name, [type], MAX(backup_finish_date)
FROM msdb..backupset
WHERE [type] = 'D' OR [type] = 'L' OR [type]='I'
GROUP BY database_name, [type]

SELECT COUNT(db_id(database_name)) AS number_of_backup_records, db_id(database_name) AS database_id, d.name DbName
FROM #tempbackup t JOIN sys.databases d ON db_id(t.database_name)=d.database_id
WHERE type = 'D' AND db_id(database_name) > 4
GROUP BY db_id(database_name), d.name
HAVING COUNT(db_id(database_name)) > 1

SQL

To resolve the issue, incorrect backup history was removed by following instructions from the link above.

EXECmsdb..sp_delete_backuphistory @oldest_date = '<current date>'

SQL

After execution of above script, we was able to successfully open the property page of databases using SSMS.