20

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.

  • 1
    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 1:53
  • 1
    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
49

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

  • 7
    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 5 '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 at 22:50
  • 1
    +1 The behavior still occurs in SSMS v17.9 and your workaround saves my day! – Panschi11291 Aug 6 at 13:50
3

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

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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