<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Restore Database Failed: 'Exclusive access...
14 posts, Page 1 of 2
1
2
»»
Restore Database Failed: 'Exclusive access could not be obtained because the database is in use.'
Rate Topic
Display Mode
Topic Options
Author
Message
SQL-DBA-01
SQL-DBA-01
Posted Monday, April 04, 2011 2:02 AM
Right there with Babe
Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 738,
Visits: 1,125
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.
Post #1087930
Grant Fritchey
Grant Fritchey
Posted Monday, April 04, 2011 6:33 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 13,372,
Visits: 25,154
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1088009
Andrew Watson-478275
Andrew Watson-478275
Posted Tuesday, April 05, 2011 2:16 AM
SSC Eights!
Group: General Forum Members
Last Login: Saturday, May 18, 2013 1:44 AM
Points: 953,
Visits: 1,875
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).
Post #1088483
SQL-DBA-01
SQL-DBA-01
Posted Tuesday, April 05, 2011 3:05 AM
Right there with Babe
Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 738,
Visits: 1,125
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.
Post #1088494
Ian Scarlett
Ian Scarlett
Posted Tuesday, April 05, 2011 3:12 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 2:12 AM
Points: 1,322,
Visits: 4,400
What is the default database for the job step running the restore?
Post #1088495
SQL-DBA-01
SQL-DBA-01
Posted Tuesday, April 05, 2011 3:16 AM
Right there with Babe
Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 738,
Visits: 1,125
master
Thanks.
Post #1088497
Grant Fritchey
Grant Fritchey
Posted Tuesday, April 05, 2011 5:17 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 13,372,
Visits: 25,154
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1088524
Jeffrey Williams 3188
Jeffrey Williams 3188
Posted Tuesday, April 05, 2011 2:17 PM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317,
Visits: 9,216
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 opportunites brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Post #1088899
Grant Fritchey
Grant Fritchey
Posted Tuesday, April 05, 2011 2:53 PM
SSChampion
Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 13,372,
Visits: 25,154
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1088934
phaniverma.dba
phaniverma.dba
Posted Tuesday, April 05, 2011 3:08 PM
Forum Newbie
Group: General Forum Members
Last Login: Friday, April 15, 2011 11:50 AM
Points: 2,
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
Post #1088942
« Prev Topic
|
Next Topic »
14 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
