Proper way to restore DB

(Michael Newland) #1

Win Server 2016
SQL Server 2016

What is the proper way to restore a database from a .bak? I have run into several issues with the infamous error “database is still in use.” The only way I have been successful is as follows:

Stop App Server
Detach database
Re-attach database
Right click DB > Tasks > Restore > Database
Select Device > Add .bak file to restore (Checked overwrite the existing database, Restore with recovery)
Database successfully restores, however, something strange happens:

If I look in the Object Explorer window, one of the databases goes to (Restoring…) and stays in that mode. I’m not sure why as I’m restoring from a .bak. Has anyone else seen this happen when restoring from a .bak file?

If anyone can offer any suggestions, they would be greatly appreciated.


(Mike Gross) #2

Sounds like you forgot to stop the task agent.

Our process is as follows:
Stop Appserver
Stop TaskAgent
Restore DB (overwrite/etc)
Regenerate Data model
Start Appserver
Start TaskAgent.

You need to make sure you are restoring from a FULL or COPY ONLY backup in case you are doing tran logs backups in between backups. And mode indicators in SSMS is normal but you should be able to monitor percent complete via the Restore window. Maybe the Obj Expl needs a refresh when the restore is finished?

(Michael Newland) #3

Thanks for the response. I didn’t think to regenerate the data model. I am also restoring from a compressed, Full backup. I did refresh the Object Explorer window and no go. I will try again today and see. It seems like during my testing yesterday, my Test environment becomes unavailable until I run a sql script to get the database out of Restoring mode. What’s strange is that the database that goes to the Restoring status is not the database I restored. It is the backup file that I restored to another database.

Hope that makes sense.

(Mike Gross) #4

To be more thorough, I start with a COPY ONLY full backup of production, compressed and verified.

Stop the Agent/Appserver and check for any other open connections using the Activity Monitor. ANY other app like Excel or any other integration can hold open connections directly to the DB that killing the appserver will not close.

The restore options include overwrite, leave operational, and fix the file name/path. It has not failed to complete that I can remember.

(Michael Newland) #5

Well, not sure what I am doing wrong. Trying to do a restore following your procedure and it still tells me database is in use. I run a sp_who2 on the database and nothing is accessing it. I’m at a loss… Checked Activity Monitor in SSMS - nada.

(Utah Taylor) #6

Are you checking “Close Existing Connections” on the restore options?

(Michael Newland) #7

Yes and when I do that it still fails stating DB is in use and then it locks the db in Single User mode. Then I have to change back to multi user with a simple sql script.

(Michael Newland) #8

OK, got it working now. If I deselect Tail-Log, it works. If I leave it enabled, it fails and says the DB is in use.

(Utah Taylor) #9

Ask Epicor for the documentation, they gave us perfect documentation which included de-selecting tail-log. I am not posting ours here because we made modifications it

(Michael Newland) #10

OK, will do! Thank you.

(Utah Taylor) #11

I found the PDF they originally sent me.Restore_101Environment_Instructions.pdf (1.1 MB) @mnewland

(Michael Newland) #12

Thank you!!!

(Brandon ) #13

You can view the ‘activity monitor’ via right clicking on the SQL instance in SSMS. That will show you which processes are keeping your database from going offline prior to restoring. Sometimes even if you shut everything down properly, I’ve had a single process get hung up and prevent the DB from going offline. You can kill the process that is hung inside of activity monitor.