SQL Server Management Studio - "Restore from Device" dialog is slow

Whenever I try to restore a database in SQL Server the dialogs take an excessively long time to open. The worst offender seems to be the "Select backup devices" modal sub-dialog from the Restore Database dialog. It usually takes 20-30 seconds to open the modal, even though it has no information and would open instantly in any other application. I've included a screenshot below.

I've found a similar question here on DBA stack exchange, but it's not an exact match, the solution doesn't work for me, and I felt this question was better suited to ServerFault since it has more to do with the management application than anything SQL related.

Can anyone tell me why this dialog is so slow to open, and (ideally) how to make it open more quickly? Thanks!

Screenshot:

Solution 1:

@Randolph West suggested this and it works for me.

Basically, when you want to restore database, don't click on the database itself. Instead, click on the folder 'Databases' and then restore.

Solution 2:

Mine went from 2.5 mins to open, to 1-2 seconds.

see https://blog.sqlauthority.com/2018/05/07/sql-server-restore-database-wizard-in-ssms-is-very-slow-to-open/

MSDB.dbo.backupset keeps a log of backups (separate to what's inside backupfiles themselves).

Try

select *
from msdb.dbo.backupset
where database_name = 'Your-DB-Name-Here'

To clean up:

EXEC sp_delete_backuphistory @oldest_date = '2019-06-27 10:00:00.000';

The date above is an example.

It is recommended to include this kind of thing in your maintenance plan.

Solution 3:

I had the same problem and I think SSMS was confused about restoring a database without any local backup history/media sets because I was trying to use backups from another server on a fresh SQL install.

The slowness went away after I created a backup of the empty database before trying to restore the real backups. This backup was enough to make the "restore from device" button respond faster.