Close

21st August 2018

Database Properties Error on SQL Server Managed Instance

I recently got this error after restoring on premise databases backups to a SQL Managed Instance and then trying to select the database properties in SSMS

picture of database properties error dialog

WTF??
Well it turns out is caused by the backup set data created when you first restored the backup file, which as correctly stated elsewhere, causes db_id() to return two rows.

My fix is to delete the backup set records for your original restore. I have tested this and it works well for me, but I take no responsibility for your actions….
First run this to get the database name and machine names you want to fix:

SELECT  DB_ID(database_name) AS [db_id(database_name)]
, database_name, backup_start_date, machine_name
FROM    msdb..backupset
WHERE   type = 'D'
ORDER BY 1, 2;

You’ll see something like this:

AN image of the query results

Backup sets in msdb

Notice the duplicate db_ids – this is what’s throwing SSMS.

Using that info run the following:

SELECT  *
FROM    msdb.dbo.backupset
WHERE
machine_name = 'MY-ORIGINAL-MACHINE-NAME (the blurred out one)'
AND  database_name = 'MY-ORIGINAL-DATABASE-NAME (the blurred out one)';

 

Take a note of the backup_set_id of this database
Now you can do this:

DECLARE @BackupSetId as INT = The id you got above
DECLARE @RestoreHistoryId as INT = (SELECT restore_history_id  FROM dbo.restorehistory WHERE backup_set_id = @BackupSetId)

-- I’m putting this in a transaction so I can see the before and after before I really press the button.
BEGIN TRAN;

-- Get the before picture
SELECT  DB_ID(database_name) AS [db_id(database_name)]
, database_name, backup_start_date, machine_name
FROM    msdb..backupset
WHERE   type = 'D'
ORDER BY 1 , 2;

DELETE  FROM dbo.backupfilegroup WHERE  backup_set_id = @BackupSetId;
DELETE  FROM dbo.backupfile WHERE   backup_set_id = @BackupSetId;
DELETE  FROM dbo.restorefilegroup WHERE restore_history_id = @RestoreHistoryId;
DELETE  FROM dbo.restorefile WHERE  restore_history_id = @RestoreHistoryId;
DELETE  FROM dbo.restorehistory WHERE   backup_set_id = @BackupSetId;
DELETE  FROM dbo.backupset WHERE backup_set_id = @BackupSetId;

-- Get the after picture
SELECT  DB_ID(database_name) AS [db_id(database_name)]
, database_name, backup_start_date, machine_name
FROM    msdb..backupset
WHERE   type = 'D'
ORDER BY 1 , 2;

-- Comment out the rollback and execute the commit when you are happy
ROLLBACK
--COMMIT;

 

Right click on your database properties and all in the world is lovely again.

Leave a Reply

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

Malcare WordPress Security