Restoring backup of Production DB over Test DB

Running into something I’ve not seen before. I made a full backup of the Product DB (named LIVE), and when I go to restore it to the test DB (MC-UAT), it’s not showing up in SQL Server Mngmnt Studio.(SSMS)

My restore procedure is:

  1. In SSMS, right click the test DB and select Task -> Restore -> database
  2. In the Restore Database window, select Device as the Source, and browse to the file I made during the backup.
  3. Under the Device field, use the Database dropdown to select the DB named ‘LIVE’

but nothing shows in the dropdown:

I’ve even tried setting the Source to Database, and selecting the MC-UAT field, before selecting the Device radio button - thinking that it was looking for the DB shown in the Database field, in the backup file of the device -> file selected.

I’ve even re-backed up the LIVE db to a new backup file, but still get the same results.

Any thoughts?

Sounds like the backup may be corrupted.

I’ll try backing it up a 3rd time. This time I’ll enable verify too.

On step 1, I was initially instructed to do “Restore Files and Filegroups”. I’m not sure the difference between that and just Restore Database.

Somethings that might be normal, or might not:

  1. As soon as I start the backup (clicking the OK button),
    a. The backup file appears in windows explorer, and is about 3.7GB.
    b. Watching the Progress indicator in SSMS, it goes from 0 to 100% in a minute or two. I assume this backing up part
    c. The progress then returns to 0%, and stays there for a minute or so. I assume this is the verify part
    d. The ‘Backup completed successfully’ message appears. The Progress never went above 0%.

  2. Normally whenever I do a restore, it takes a good 5 minutes for the ‘Restore database’ window to appear, after selecting Tasks -> Restore -> Database. I’ve been told it is because it is searching for backup files, and takes that long to find (and catalog?) them.

I always used “Tasks -> Restore -> Database” , as that’s what is in the Sys Admin Guide:

But then again, that also says to select the Database Radio button, then use the browse to find the file. But that browse button is only enabled if you select the Device radio button.


Hmmm… Seems the two Domain accounts I have - which are supposed to be identical - aren’t.

I was given two domain accounts (<my_userid> and exas_<my_userid>) and told they were completely interchangeable. And that I should start using the exas_ prefixed account for all administrative functions.

As the exas_... account, I could

  • Login into the SQL server
  • Launch SSMS
  • Connect to the DB
  • Perform a backup.

Buts only when I used the original account, could I see the DB in the backup file created when I logged in as exas_...

Time to ask the IT folks what is up, and that I wasted several hours trying to do this simple copy of LIVE to TEST.