SQL Server 2012 Management Studio hangs

Question

SSMS 2012 version 11.0.3128.0

I am trying to restore a database from a device, but SSMS keeps hanging before I get there...

I do the following:

• Right-Click Databases
• Select Restore Database...
• Select Device to bring up Select Backup Devices dialog
• Click Add

and that is where it hangs...

Just discovered that the same happens if I try to access

Restore Databases > Files section

it looks like trying to access the file system is an issue.

After about half an hour got a time out error followed by this:

How can I troubleshoot this?

UPDATE In this case it turned out that a server restart solved the issue.

Perhaps that is the first thing one should try (if its feasible to restart the server that is).

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.

Answer 2

The actual solution in this case was a simple restart of the server.

I would recommend that this is the first thing that is tried if this is at all possible.

Answer 3

Try it directly on the server. If that still gives you issues then do you have a lot of backups that have been taken?

If so it can take a very long time for the database engine to return the information requested. Because of this it is recommended that you always clean out the old data from the msdb database. Microsoft provides a stored procedure to do this which is called sp_delete_backuphistory. When you have a lot of backup history to process (like you may now) it'll take a long time to run.