I have a development laptop with SSMS Express 2012 with a 2012 db instance and a 2008 db instance. Have been using this configuration for over a year. Suddenly I am unable to use the restore wizard. The wizard will pick the backup file but when I select the "Files" option on the upper left to designate the location of the MDF and LDF the dialog hangs. I tried a repair, no luck.
5 Answers
@Pat I have been facing the same issue for a long time, but a few minutes ago found way around it.
First of all DO NOT try to restore by right-clicking on an empty database. What you need to do is right-click on Databases and from the menu select Restore Database. In that UI you can use the Files option and the UI won't freeze.
Note: By doing this SQL will create your DB and restore into it in one go.
Hope it helps.
-
8Wow... Why down voting this answer. After seamlessly trying to restore a backup on a newly created blank database I tried this as a last resort. This is the only solution that worked for me!– AndezCommented Mar 24, 2016 at 21:00
-
3Also don't know why this was downvoted. Successfully worked for me to after the same issue kept freezing SSMS 2014. I have also encountered this in SSMS 2012– Vort3xCommented Sep 5, 2016 at 11:24
-
3+1 Works for me on SSMS 17.5 as well, on which I still see this problem when browsing for the backup files. Commented Feb 22, 2018 at 15:57
-
-
1+1 The behavior still occurs in SSMS v17.9 and your workaround saves my day! Commented Aug 6, 2019 at 13:50
You can try to restore via T-SQL. E.g.:
RESTORE DATABASE YourDatabase
FROM DISK = 'C:\BackUp\Full.BAK'
WITH
REPLACE -- Overwrite DB - if one exists
, NORECOVERY -- Use if DIFFs / T/Logs to recover
-- , RECOVERY -- Use if NO more files to recover, database will be set ready to use
, STATS = 10 -- Show progress (every 10%)
GO
As for the wizard error you can try to use Windows Event Viewer to try troubleshooting
I too had SSMS freeze immediately after selecting a database to restore.
The fix for me was simple, I just had to run SSMS as an Administrator.
I hope this helps someone else.
I had this same problem today, trying to restore several files of database X as a new database set as the destination.
The problem in my case was that the backups were for a database X (Full+Diff+Logs), and there already was database X on the server, but the database was offline at the moment. That caused SMSS to freeze every time. So to solve this, I just temporarily brought database X back online, done the restore to a new database, and then took database X back offline.
Hopefully this might help someone experiencing this issue. If the Restore Database dialog hangs and is not responding, check if there isn't an offline database with the same name as the one in the backups.
I have also had this problem even when not using the wizard, using a script similar to Marko's running through SSMS it would freeze and crash the machine. The way I resolved it was to drop the DB first, then I found the .ldf to still be in the target directory, removed the .ldf from the target directory and was able to restore successfully.
RESTORE HEADERONLYandRESTORE VERIFYONLYsee if it works. Also, use T-SQL like @marko suggested.