5

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. – Daniel Björk Aug 22 '17 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. – Randolph West Aug 23 '17 at 17:25
  • 2
    Can you verify how much data you have in you backup history tables? – Tom V Aug 24 '17 at 7:35
2

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

0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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