6

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 - try topanswers.xyz Aug 24 '17 at 7:35
1

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.

| improve this answer | |
  • 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 – Keith E. Truesdell Jun 18 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. – Keith E. Truesdell Jun 18 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. – Keith E. Truesdell Jun 18 at 18:02
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

| improve this answer | |
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.

| improve this answer | |

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.