SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore Database Failed: 'Exclusive access could not be obtained because the database is in use.'


Restore Database Failed: 'Exclusive access could not be obtained because the database is in use.'

Author
Message
SQL-DBA-01
SQL-DBA-01
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4578 Visits: 3397
Hi,

While trying to restore a database (with replace option), I am getting the below error -

Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Though, before kicking off the refresh, I took the database in 'single user' mode and also used the below script to KILL all the connected sessions.

DECLARE @SPIDSTR varchar(8000)
DECLARE @ConnKilled smallint
DECLARE @DBName varchar(100)

SET @SPIDSTR = ''
SET @ConnKilled = 0;
SET @DBName = '<database>'

SELECT @SPIDSTR=coalesce(@spidstr,',' )+'KILL '+convert(varchar, spid)+ '; '
FROM MASTER..SYSPROCESSES WHERE dbid=db_id(@DBName);

Print @SPIDSTR;
IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr);
Print @SPIDSTR;
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

END

Can you please suggest, what went wrong, in my adhered process?

Atlast, I had to drop the database and now when I am trying to restore, it is working fine.

Kindly suggest with your opinion.

Thanks.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55825 Visits: 32798
Even if you place it in single user mode, if you're connected to that database or another connection is there, it might still be active when you try to run the backup. Generally I don't use single user mode, I use restricted user, but that assumes that most of our logins are not 'sa' and the ones that are I can kill or identify and alert individually.

----------------------------------------------------
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
Andrew Watson-478275
Andrew Watson-478275
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1921 Visits: 2719
Are you doing the restore from SSMS rather than a query? When SSMS opens its restore window, it starts a session using your default database. If this is the database you're trying to restore, you get the error described. (This has always annoyed me).
SQL-DBA-01
SQL-DBA-01
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4578 Visits: 3397
no, refresh of the database is being performed using a SQL JOB. I dont use the SSMS generally for this kind of tasks, it is prone to hang for large size bkps.

Thanks.
Ian Scarlett
Ian Scarlett
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2817 Visits: 7055
What is the default database for the job step running the restore?



SQL-DBA-01
SQL-DBA-01
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4578 Visits: 3397
master

Thanks.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55825 Visits: 32798
Can you try using restricted user instead of single user? I've seen applications that constantly refresh their connection so they can squeek in before you start the restore.

----------------------------------------------------
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
Jeffrey Williams 3188
Jeffrey Williams 3188
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10105 Visits: 10004
Grant Fritchey (4/5/2011)
Can you try using restricted user instead of single user? I've seen applications that constantly refresh their connection so they can squeek in before you start the restore.


If this doesn't work, then you have some process refreshing its connection and is using a privileged account.

I prefer taking the database offline myself - this insures that nobody can access that database and allows the restore to proceed with no problems.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55825 Visits: 32798
Jeffrey Williams-493691 (4/5/2011)
Grant Fritchey (4/5/2011)
Can you try using restricted user instead of single user? I've seen applications that constantly refresh their connection so they can squeek in before you start the restore.


If this doesn't work, then you have some process refreshing its connection and is using a privileged account.

I prefer taking the database offline myself - this insures that nobody can access that database and allows the restore to proceed with no problems.


True, but I prefer to not let anyone have privileged access to the server except other DBAs.

----------------------------------------------------
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
phaniverma.dba
phaniverma.dba
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 10
Hi Sourav Mukherjee,

You can use the below command.

ALTER DATABASE [mydb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [mydb] FROM DISK='f:\backup\mydb.bak'
GO
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search