Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Pamela Stephenson]

I am trying to restore a backup of the live company to a test/historical company in Dynamics GP 18 using SQL Server Management Studio, with SQL 2017.  I keep getting an error, saying

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended)

 

I have tried it with the option to close existing destination database marked, and with all users out of Dynamics.  We do use Employee self service, so not sure if that is an issue.  Any help would be appreciated.

Thank you.

 

——————————
Pam Stephenson
Director of Accounting and Finance
Ashley Ward, Inc.
Mason OH
——————————

[Jamie Evenson]

Hi Pam, 

In cases where everyone is out of GP and even when marking the “Close existing connections…” checkbox and you still don’t have exclusive rights to restore, I would look at the Activity Monitor.  If you are in SSMS, follow the below instructions and see if you see a connection still open to the destination database: 

• Right click on your server instance and choose Activity Monitor.   
• When the Activity Monitor opens, expand Processes. 
• In the Database column, select the dropdown and check if your destination database is visible in this list. 
• Once you select the database, the connections that are available are returned. 
  • If you are certain the connection should not be available, you can right click on the connection and choose Kill Process. 
  • IMPORTANT: Definitely be cautious in this window so as not to kill an active connection that should be active.  

Let me know if you have any questions or concerns with the above information. 

Thank you, 
Jamie

——————————
Jamie Evenson
CEO
Najaxa Software
Fargo, ND
——————————
——————————————-

[Thaddeus Suter]

Here are the straight up SQL commands to find and kill.

Sometimes the connection(s) has nothing to do with GP and can be an integration service or even Management Reporter that is running on some other server and then you will want to stop the service(s) because even though you kill the connection(s) it will keep coming back…real fast…

–this command finds the connections…you will see all databases
Exec sp_who
go

–this command kills  and using the SPID is database independent
Kill <SPID>;
go

——————————
Thaddeus Suter
Retus, Inc
HELOTES TX
——————————
——————————————-

[Mark Wiley]

Hi Pam,

You have some good options, and that would be my first choice. If you are running Management Reporter, that might be your culprit. Here are some other things to consider:

On your restore:

• Tick the box for “Overwrite the existing database (WITH REPLACE)”
• Un-tick the box for “Take Tail-log backup before restore”
• Tick the box for “Close existing connections to destination database”

FOR SQL RESTORE:
USE master
GO

ALTER DATABASE [TWO] –Your database name
SET SINGLE_USER –This rolls back all uncommitted transactions in the db.
WITH ROLLBACK IMMEDIATE
GO

–Restore your database

ALTER DATABASE [TWO] –Your database name
SET MULTI_USER –Ready for all users
GO

One more option, if “SINGLE_USER” doesn’t work, you can take the database off-line. Execute this query before restoring database:
ALTER DATABASE [TWO] –Your database name
SET OFFLINE WITH ROLLBACK IMMEDIATE

…and this one after restoring:
ALTER DATABASE [TWO] –Your database name
SET ONLINE

Hope this helps.

——————————
Mark Wiley
Dynamics Credentialed Professional
Senior Solutions Architect
GraVoc
Ohio Office
——————————
——————————————-

[Pamela Stephenson]

Thank you everyone for all the advice.  I ended up rebooting the server, closing the management reporter services, then the restore worked!  It got stuck keeping the original live data in a restore mode, for some reason.  But we backed up that database, and it took it off the “restoring” setting it was stuck at.

Thanks!

——————————
Pam Stephenson
Director of Accounting and Finance
Ashley Ward, Inc.
Mason OH
——————————
——————————————-