SQL server 2012 restore wizard freezes

Question

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.

Why are you not using T-SQL script to restore ? Wizard does not have proper wisdom any more and so it hangs — Kin Shah, Feb 27 '14 at 01:53
I have seen the restore wizard hang when an incorrectly formed backup files was selected in the past. Do a RESTORE HEADERONLYand RESTORE VERIFYONLY see if it works. Also, use T-SQL like @marko suggested. — RK Kuppala, Mar 31 '14 at 17:24

score 62 · Answer 1 · edited Sep 05 '16 at 11:58

62

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

edited Sep 05 '16 at 11:58

Andriy M

22,983
6
59
103

answered Feb 25 '16 at 10:47

pi4r0n

741
5
2

8

Wow... 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! – Andez Mar 24 '16 at 21:00
3

Also 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 – Vort3x Sep 05 '16 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. – squillman Feb 22 '18 at 15:57
WWWWHYYYYYYYYYYYYYY????? – hanzolo Jun 25 '19 at 22:50
your solution saved my day, thanks ! – Reza Abolfathi Jun 29 '19 at 10:35
1

+1 The behavior still occurs in SSMS v17.9 and your workaround saves my day! – Panschi11291 Aug 06 '19 at 13:50
Still occurs in v18.2. How has MSFT not fixed this yet?? Thank you for the workaround! – VenerableAgents Sep 06 '19 at 20:16
Here I am in 2020, using SSMS 18, with this solution from 2016 being the only workaround... – justiceorjustus Apr 13 '20 at 22:47
worked perfectly, for me it was happenig in MSSQL 2017. Thnx – ramya May 26 '23 at 10:07

score 3 · Answer 2 · answered Mar 31 '14 at 17:16

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

score 2 · Answer 3 · answered Sep 09 '17 at 01:35

2

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.

answered Sep 09 '17 at 01:35

Ken Haynes

21
2

Tom Pažourek · Answer 4 · 2017-06-28T20:36:22.817

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.

score 0 · Answer 5 · answered Jan 15 '20 at 23:13

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.

SQL server 2012 restore wizard freezes

5 Answers5