15

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
Improve this question
5
  • No, COD hotfixes are not installed with Windows Update unless they are critical security issues. The download link works for me, try a different browser, but here's a direct link for you in any case, assuming x64 operating system.
    – Aaron Bertrand
    Jan 5, 2016 at 21:18
  • Thanks, I got the hotfix installed, but that did not solve the problem. I think I may just have to uninstall and reinstall when I get time, and just use the old version of SSMS until then.
    – Josh Bula
    Jan 6, 2016 at 21:34
  • Have you tried installing SSMS 2016?
    – Elsimer
    Feb 6, 2017 at 4:30
  • This happens to me sometimes when I work from home and connected via VPN. I think it's just a little laggy, but eventually the window pops up.
    – pintosack
    Aug 14, 2018 at 13:40
  • I have the same issue in version 18 of ssms and sql server 2017.
    – boggy
    Apr 12, 2022 at 20:39

4 Answers 4

Reset to default
4

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

Improve this answer
4

In my case I just wanted to restore a database from device (full backup, copy-only). So this workaround worked for me. Right-click "Databases" and go to "Restore Database..." (instead of selecting the database). This should open up the wizard because there is no history to be loaded.

Within the options I selected "Overwrite the existing database" and I deselected "Take tail-log backup before restore".

Improve this answer
3

Found this on another thread and it worked for me:

SQL server studio 2014 doesn't like error "Unable to create a restore plan due to break in the LSN chain". And fails to open restore dialog. Fix LSN chain by issuing FULL backup. After that restore dialog should work.

https://serverfault.com/questions/705398/mssql-management-studio-2014-not-showing-the-tasksrestoredatabase-dialog

Improve this answer
1

In case anyone is still having the same issue

Nothing described here help me to fix my problem, however I was able to workaround the problem performing a backup of my empty database and then magically the add button worked and let me do a restore from my bak file

My environment is:

  • Windows 10 enterprise, clean installation
  • SQL Server 2017 express edition, clean installation
  • SQL Server Management Studio Ver 18.1, clean installation

I don't know for fact but I think the root cause of my problem is: during installation, I set a different path for data files (not the default under "/program files", and SQL or SSMS was probably trying to access the default (nonexistent) location the first time.

Improve this answer

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