Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Restore Database Failed: 'Exclusive access could not be obtained because the database is in use.' Expand / Collapse
Author
Message
Posted Tuesday, April 5, 2011 7:35 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, September 16, 2016 1:22 PM
Points: 4,377, Visits: 9,691
Grant Fritchey (4/5/2011)
True, but I prefer to not let anyone have privileged access to the server except other DBAs.


Of course, I would prefer that also - but I have various systems where the vendors have setup their applications to use privileged accounts. I don't have a choice...unfortunately.


Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1088989
Posted Thursday, September 27, 2012 12:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 10, 2015 7:05 AM
Points: 1, Visits: 12
In my case: I was logged in to SSMS as 'user_x'. Default database for 'user_x' was 'Test_DB'.
I got same errormessage when I tried to restore in this session 'Test_DB'.

When I logged out as 'user_x' and logged in SSMS as another user (that did not have default database 'Test_DB'), restore was successful.
Post #1365048
Posted Thursday, September 27, 2012 12:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:20 PM
Points: 2,212, Visits: 3,000
Good.

Thanks.
Post #1365051
Posted Thursday, September 27, 2012 4:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 175, Visits: 547
Make sure no other instances of SSMS are open.
If you have multiple tabs open, make sure they are not pointing to the DB in question.

You could also try restarting the service.

Any replication going on? Or any other jobs or triggers pointing to your troublesome DB?




For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1365136
Posted Tuesday, July 29, 2014 11:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 16, 2016 10:22 AM
Points: 1,574, Visits: 2,958
Dennis Post (9/27/2012)
...
If you have multiple tabs open, make sure they are not pointing to the DB in question...


This helped and fixed the problem, thank you

(Using... MSSQL 2012 DEV EDT)
Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

(after logs growth due to uncommitted transaction and also data was being inserted at the same time.. the log is in a huge size and adding additional log then backup-ing the log and restoring the database full backup and restoring the recent log..... all things came to normal)


ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Post #1597412
Posted Wednesday, July 30, 2014 5:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 2:39 AM
Points: 3, Visits: 6

What is the default database for the job step running the restore?


daily deals, online shopping sites, hot deals, best deals
Post #1597661
Posted Wednesday, July 30, 2014 6:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 16, 2016 10:22 AM
Points: 1,574, Visits: 2,958
samanta0rodriges (7/30/2014)

What is the default database for the job step running the restore?


basically and mainly, the usage of the default DB points to "MASTER", as I did the same to.

Are you also facing the same problem?

(if no then great, if yes... then post any errors appeared and steps here so we can also learn from that, thank you)


ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Post #1597698
Posted Monday, April 4, 2016 5:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:50 PM
Points: 290, Visits: 577
you must set the database to single user mode before attempting to restore see here: http://www.sqlserverlogexplorer.com/exclusive-access-not-obtained-database-use/
Post #1774628
Posted Monday, April 4, 2016 6:52 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 12:32 PM
Points: 16,950, Visits: 31,767
jacksonandrew321 (4/4/2016)
you must set the database to single user mode before attempting to restore see here: http://www.sqlserverlogexplorer.com/exclusive-access-not-obtained-database-use/


This is a two year old thread.

You don't have to set the database to single user in order to restore a database. You do have to have exclusive access to the database you're restoring. One way of doing that can be to set the database to single user, although, if another connection is already there, that can cause problems. I generally go for restricted user, but, in this case, the third party apps all need privileged access, so that won't work either. Best approach here is likely just to kill every connection first.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1774672
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse