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
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8244 Visits: 3564
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 (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97715 Visits: 33014
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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2201 Visits: 2753
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
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8244 Visits: 3564
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
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

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



SQL-DBA-01
SQL-DBA-01
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8244 Visits: 3564
master

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

Group: General Forum Members
Points: 97715 Visits: 33014
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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18949 Visits: 10042
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 (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97715 Visits: 33014
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
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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