8

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:

Screenshot of the restore dialog and its child modal

Jake
  • 264
  • 1
  • 9
  • 1
    It’s probably enumerating devices / drives. So start by suspecting an anti-virus, or faulty mapped network drives, or non-responsive drives showing up on the system. – Appleoddity May 08 '18 at 23:22
  • @Appleoddity I don't have any network drives. The only drives on my system are high performance M.2 SSDs that open instantly in File Explorer. They do have bitlocker enabled but that should be abstracted away from SQL Server for the purpose of reading the file system. – Jake May 09 '18 at 01:28

3 Answers3

3

@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.

FrenkyB
  • 130
  • 5
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.

Kind Contributor
  • 186
  • 6
1

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.

Jacob
  • 111
  • 3
  • So obvious and yest so Microsoft. Works flawlessly! Must be an issue with network name resolution of ServerName property unaccessible on the device restoring the database. – too May 13 '20 at 12:30