Sql-server – Restore Database wizard won’t open in SSMS 2014

restoresql serversql server 2014ssms

On a dev machine running Windows Server 2012 R2, I just upgraded SQL Server from 2012 to 2014.

Using SQL Server Management Studio 2014, when I right-click a database and go to Tasks -> Restore -> Database... nothing happens. The Restore Database wizard does not appear as expected.

No errors or crashes are in Event Viewer. The cursor doesn't even turn into an hourglass.

Using T-SQL to restore works fine, and if I go back and use SSMS 2012 it works fine. Any ideas on how to get the wizard to work in SSMS 2014?

I am connecting to the same instance of SQL Server in both versions of SSMS, and I have tried running SSMS as an administrator.

It is Service Pack 1:

Microsoft SQL Server Management Studio 12.0.4100.1
Microsoft Analysis Services Client Tools 12.0.4100.1
Microsoft Data Access Components (MDAC) 6.3.9600.17415
Microsoft MSXML 3.0 5.0 6.0 
Microsoft Internet Explorer 9.11.9600.18125
Microsoft .NET Framework 4.0.30319.34209
Operating System 6.3.9600

Best Answer

I have seen that behavior myself in both SSMS 2012 and SSMS 2014. What it typically means is that the backup / restore history for the database is very long and it is taking a long time for the wizard to come up, because it needs to read the entire history.

Make sure you have a maintenance plan that truncates the history. To find out, run this query:

SELECT TOP 1 backup_start_date
FROM msdb.dbo.backupset WITH (NOLOCK)
ORDER BY backup_set_id ASC

That query will be quick, since it's sorted by the primary clustered key of the table. If you've got more than a month or two of history in there, it's probably time to clear it out. (Keep in mind that clearing the history will involve locks, so it can be a slow process if you're doing backups at the same time, or if your MSDB is on slow storage.)

Related Question