Proper way to restore DB

Epicor ERP 10
1

Win Server 2016
SQL Server 2016
Epicor 10.2.200.6

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.

Thanks.

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?

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.

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.

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.

6

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

2 Likes
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.

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.

3 Likes
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

1 Like
10

OK, will do! Thank you.

11

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

4 Likes
12

Thank you!!!

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.

image

1 Like
14

I made it most of the way through this. I completed the restore and started the application pool, but when I go to test it shows not connected. Did I miss something?

15

Check to see if you left the DB in single user mode.

1 Like
16

I found this online @KPreda to check for single user mode

Select ServerProperty(‘IsSingleUser’)

Server is in single-user mode.

1 = Single user.

0 = Not single user

17

Thanks, but it was set at multi user not single user. I set it to single user and figured I could change it back, but now i can’t get into it at all.

18

We ended up using this to set it to multi user:

Set a database to single-user mode - SQL Server | Microsoft Docs

USE master;
GO
ALTER DATABASE AdventureWorks2012 (replace with your db name)
SET MULTI_USER;
GO

1 Like
19

I just used the attached procedure from utaylor above, and did not need to check the close existing connections since i’d already done that via the admin console.

Booted right back up in mutli-user mode like a dream. Thanks for the guide!

1 Like