sql-serverssmsdatabase-backupsdatabase-restore

How can I restore my SQL Server database in MSSMS?


I backed up my database, but chose to save it to a location of my own on my C drive, rather than the default location. From there I also copied it to a USB stick. So I have the old data. But how can I restore it?

I accidentally deleted a LOT of records that I need back. I see no "Undo" option in MSSMS, nor a "Rollback" option.

That was my first thought (to try to Undo or Rollback). So then I went to do a "Restore" but it failed.

Here is what I have tried:

enter image description here

I tried with the default settings which appeared, but after I clicked the OK button I got:

enter image description here

I thought this was probably because I didn't backup to the default location, but to my hard drive. So I tried getting to the backup file on my C drive by selecting the "Device" radio button and then the ellipsis dots button here:

enter image description here

...and then selected the file from the folder where I put it:

enter image description here

...but I still got "Restore Failed"

My next attempt (and the last thing I know to try) was to go to the usb stick (drive D).

So I clicked the ellipsis dot button again, removed the previous entry (C Drive) and added the D/usb stick, and selected the same file:

enter image description here

I selected OK there, and then OK again here:

enter image description here

...but the third time's a curse, so to speak: again, I got "Restore of Database failed"

enter image description here

I also tried restoring, not the database, but the files:

enter image description here

...but got this:

enter image description here

It must be possible to restore my database - after all, I do have two copies of it backed up. But how?


Solution

  • Because you were looking at it in SSMS, the database was considered in use.

    You have two options.

    The latter is MUCH safer and my preferred option unless you are happy doing a complete rollback to the time it was when you took the backup.

    If you are using the first option, I strongly suggest taking a backup of the current database before doing a restore to the old version, in case you've done some work (e.g., created procedures etc) that you want back.