SSMS Restore Database wizard slow to respond
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."
ALTER DATABASE [DBToRestore]
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [DBToRestore]
SET ONLINE
GO
RESTORE DATABASE [DBToRestore] FROM DISK = N'Directory\SourceDBbackup.bak' --**Replace this value with the most recent FULL Backup file (BAK)
WITH
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.
GO
--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
RESTORE DATABASE [DBToRestore] WITH RECOVERY;
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql
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 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.