I've just noticed this recently on one of the SQL Server 2012 SP3 instances that I oversee.

When I try to restore a database using SSMS 2012 (by right-clicking on the database,and choosing Tasks>Restore>Database), the Restore Database window (where you can pick the source, and destination database, etc.) takes a very long time before it finally shows up on the screen. I'm talking more than 10 minutes here at times.

I've noticed this both using my local SSMS, and on the SSMS installed on the Windows Server where the SQL Server instance is installed.

I am only noticing this on one specific instance. I don't see the same slow response issue when doing a Restore on the gui on the other instances.

Have any of you seen this before? I'd appreciate the help. Thanks.

  • 6
    When the wizard is slow its usually because you have a large backup history loading. Commented Aug 22, 2017 at 18:38
  • 2
    To make it go (much) faster, right-click on the "Databases" node, not the database itself. You'll have to manually navigate to the path where the backups reside, instead of just picking a backup from the history, but it's a whole lot quicker. Also the T-SQL method below, which is basically the same thing. Commented Aug 23, 2017 at 17:25
  • 2
    Can you verify how much data you have in you backup history tables?
    – Tom V
    Commented Aug 24, 2017 at 7:35

5 Answers 5


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


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

To clean up:

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

WARNING: the sp_delete_backuphistory procedure isn't limited to one database, there are no other parameters for this procedure call

The date above is an example.

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

  • As a small edit, just make sure you are on the msdb for that sproc. It may seem obvious, but i was on the database I was trying to restore and it tripped me up for a couple minutes wondering if I had an older version of SSMS or SQL Server that doesn't have that sproc Commented Jun 18, 2020 at 17:57
  • Also, i agree that it should be part of your maintenance plan. We just switched from a vendor doing the backups to Ola's scripts and I am surprised that this was not (from what I could see) in Ola's scripts. Unless I am missing something, maybe a default we switched turned that off or it was something leftover from the vendor. Commented Jun 18, 2020 at 17:59
  • 1
    Sorry, one more thing, this was the perfect solution, thank you. But to add something else, the sproc 'sp_delete_backuphistory` will delete all history for all DBs older than that @oldest_date variable. One may want to have older history on some DBs than others. This sproc will not do that based on the DB. Commented Jun 18, 2020 at 18:02

Does the wizard show you stats as you go?

Have you tried using T-SQL to restore? The wizard is probably that way because it's probably just busy in the background, which is certainly the case if your bak file is big (or trn files are plentiful). The script below will show you stats every 5% in the "Messages" tab.

Might also help to take the database offline prior to a restore (ensure there are no users while doing this, or it will "do it for you"):

--Taking DB Offline due to errors received without this.
--Error: "Exclusive access could not be obtained because the database is in use."


RESTORE DATABASE [DBToRestore] FROM  DISK = N'Directory\SourceDBbackup.bak' --**Replace this value with the most recent FULL Backup file (BAK)
FILE = 1
,  NORECOVERY  --If Transaction Logs are available, use this
,  NOUNLOAD   --Tape remains loaded on the tape drive.
,  REPLACE    --Replace the destination database.
,  STATS = 5  --Displays stats update message for every ## Percent complete.


--Finally, "restore" the database without actually restoring the data.
--This is done post-full restore in order to bring a database back to working order. (Technically optional)
--Details: https://msdn.microsoft.com/en-us/library/ms188696.aspx




It may be due to CPU usage. Check out if there are any applications using memory. Then check Disk usage.

And if CPU usage , memory consumption and Disk usage is not so high then try out TSQL script which may workout for you.


There is a way to delete the backuphistory for single databases:

USE msdb;
EXEC sp_delete_database_backuphistory
    @database_name = 'your_db_nbame';


My database restore was stuck "Creating restore plan - reading backupset header" step.

I'm on Sql Server 2017, same was SSMS.

I downloaded new version of SSMS - 18.11.1 ( https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms ) and restore processed immediately.

Looks like there is some issue with older SSMS version and it's not necessarily related to you SQL server or database itself.

  • The question is about SQL Server 2012. Your answer is for SQL Server 2017. This might not be the same issue and could be just an SSMS issue with 17. vs. 18. The issue seems to have been fixed with 18.5 General SSMS Addressed one more case where Restore dialog hangs when selecting the Browse button.
    – John K. N.
    Commented Apr 11, 2022 at 12:35
  • @JohnK.N. you are absolutely right that the original question was about 2012. But I got here looking for solution without specifying SSMS version and I guess plenty of other users experiencing same issue may end up here as well, despite the fact they are on other versions. I included in my answer which version I was on, and what helped me. I added my answer so if anyone else gets here, maybe that would help them as well. Commented Apr 11, 2022 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.