SQL Server Management Studio: Restore Database from Device HANGS on clicking "..."
When I wish to restore a database from a .back file, I go into SSMS, select Tasks->Restore Database, and on the dialog, I click the "From Device" radio button, and then click the "..." button to browse to the .bak file I wish to restore. This then HANGS for MINUTES before the "select a backup device" dialog appears and allows me to drill down and select the backup file.
I have literally HUNDREDS and HUNDREDS (maybe THOUSANDS) of *.bak files on a big data drive with lots of folders. It seems like it might be trying to iterate over all of them and pull information from each file before displaying the dialog. Just a guess, but I can't imagine what else it's trying to do. And it shouldn't need to do that.
It's pretty much unusable. I have to click the "..." and go get like three cups of coffee before it returns and I can continue with my work.
Please, try SSMS 18.3.1+
This should be fixed.
Diego Esteves commented
Estava com o mesmo problema, SSMS 18.2, não abria nunca a janela para restauração de banco de dados.
Consegui abrir a janela para restauração seguindo a dica aqui do fórum:
criei um backup do banco novo vazio, após fazer isso, consegui restaurara qualquer arquivo .bak.
Ivo B commented
You can avoid this problem by right clicking on the Databases node and choose the restore option from here, instead of choosing the restore option on the database itself. Works the same, but without the freeze.
Back again for me too as of 7/1/2020
Problem is back in 18.4
++Anthony. Backup the empty db first.
Adrian Evans commented
I installed 18.4, but also did a backup of the company before I tried restoring, it is as though the system tries to look for back-up files and hangs if it doesn't find any if you have never done a backup of the database.
Just updated to 18.3.1. SSMS freeze then click on Add button. Can't get any select dialog at all...
What I do for this is when the empty database is created. do a backup of the empty database, and then it worked with no issues.
I regret upgrading, taking ages for source->devices dialog to open(still not returned) and I have but 1 database. v15.0.18142
I have the same issue and I have only TWO .bak files
SQL Server Management Studio 15.0.18142.0
Same issue here (running SSMS v18.2) ...
I'm having the same issue. SQL 2018 on Windows Server 2016. New Install. Only 3 files in the backup folder. Does the same if I have no files. Is there a fix yet?
I was able to restore DB after I did dummy backup in the different folder then default, then the restore of a backup I needed was successfull.
Mohanjana Dissanayake commented
I have change the permission to database default backup location and issue was fixed.
I have the same problem on Windows 10 on Parallels with Microsoft SQL Server Managament Studio 18.1. When I click ... to choose device Restore Database Dialog freezes.
I confirm that this only happens if I right click on the database name and select "Tasks -> Restore" but it does not happen if I right click on "Databases" and select "Restore Database" I have <10 backup files in the folder, so this isn't a problem of having too many backup files. It's not even at the step where it iterates the folder yet, that comes after you click the "Add" button not the "..." button.
SQL Server 2016 and SQL Server Management Studio V17.2 (SSMS Component version is 14.0.17177.0).
In what version of SSMS does this actually work normally?
This is also true for me... THIS IS ****!!!
How are we on 18.2 and this still isn't fixed? I right-click a database, select Task --> Restore, and click "From device" and click "..." to select the backup file AND IT JUST HANGS FOREVER, NOT RESPONDING.
How does something like this get past your testing and QA? How is it acceptable it's been like this since 18.0's release and still hasn't been fixed?
It worked perfectly in SSMS 2008R2. With each subsequent version, it got slower. In 17.9.x it can take a minute or more, but at least it comes back. But now in 18.x, it just doesn't even work.
Restoring databases is a core function of a SQL Database, and it's fundamentally broken in this tool. I have mandated that nobody update past 17.9.x until this is fixed. Which is terrible, because 18.0 has other features I want (for example, object scripting has been broken in every version after 2008R2 as well, but finally fixed in 18.0). I have to keep 2008R2's SSMS around just to be able to do simple and obvious things quickly and efficiently.
WHEN CAN WE GET THIS FIXED?
Ewen Miller commented
It does it on both the Databases and actual data base in Object Explorer. I am using SSMS v18.2.
I am trying to do an out of place restore from blob storage.
Running select *
1) where database_name = 'mDB name'
As a work around I have. Restored the full backup from the blob storage and then then tried to restore the 60 Tx subsequent Transaction log backups - same issue, hangs at the same page -i.e when the device radio button is selected and the square button containing the three does '...' is pressed,. Pretty impractical so rewtore each of the 60 Tx logs using T-SQL - get the same issue anyway.