• 12
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

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

      • 1
    • @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.
      • 2
    • 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.

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

  • 3
Reply Report

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.

  • 1
Reply Report

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.

  • 1
Reply Report
    • 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.

Trending Tags