?
Solved

How do I run a recovery from query analyzer

Posted on 2004-09-21
16
Medium Priority
?
511 Views
Last Modified: 2012-05-05
I am testing code for backup/recovery in SQL Server 2000.  While attempting to run the code in Query Analyzer, I receive an error message:

"Exclusive access could not be obtained because the database is in use."

I've checked with the sp_who and I see that the query analyzer is the ONLY process that is connected to the database.  

Is this an issue where I need to be in single user mode?  Does that matter?  If so, while I am testing in single user mode, wouldn't that also keep others from connecting to my server?  Or is single user specific to the database rather than the server as a whole?

The code is fairly simplistic and I'm trying to experiment and learn how to do this with T-SQL and EM.  

I've included the code below:  (also, the response in Query Analyzer is below the code)

use master
go

sp_addumpdevice 'disk', 'log_dump0', 'd:\tmp\sql_backup\logdum0.dat'
go
sp_addumpdevice 'disk', 'log_dump1', 'd:\tmp\sql_backup\logdum1.dat'
go
sp_addumpdevice 'disk', 'log_dump2', 'd:\tmp\sql_backup\logdum2.dat'
go
sp_addumpdevice 'disk', 'log_dump3', 'd:\tmp\sql_backup\logdum3.dat'
go
sp_addumpdevice 'disk', 'log_dump4', 'd:\tmp\sql_backup\logdum4.dat'
go

sp_addumpdevice 'disk', 'full_backup', 'd:\tmp\sql_backup\full_backup.dat'
go

use mcbath
go
drop table test
go
create table test (cola int)
go
insert into test values(1)
go
insert into test values(2)
go
insert into test values(3)
go
insert into test values(4)
go
insert into test values(5)
go
insert into test values(6)
go
backup transaction mcbath to log_dump0
go
insert into test values(7)
go
insert into test values(8)
go
backup transaction mcbath to log_dump1
go
insert into test values(9)
go
insert into test values(10)
go
insert into test values(11)
go
insert into test values(12)
go
backup transaction mcbath to log_dump2
go
insert into test values(13)
go
insert into test values(14)
go
insert into test values(15)
go
backup transaction mcbath to log_dump3
go
insert into test values(16)
go
insert into test values(17)
go
insert into test values(18)
go
backup transaction mcbath to log_dump4
go
insert into test values(19)
go

delete from test
go

restore database mcbath from full_backup with norecovery
go
restore log mcbath from log_dump0 with norecovery
go
restore log mcbath from log_dump1 with norecovery
go
restore log mcbath from log_dump2 with norecovery
go
restore log mcbath from log_dump3 with norecovery
go
restore log mcbath from log_dump4 with norecovery
go

use mcbath
go

SELECT * FROM test
go


sp_dropdevice 'log_dump0'
go
sp_dropdevice 'log_dump1'
go
sp_dropdevice 'log_dump2'
go
sp_dropdevice 'log_dump3'
go
sp_dropdevice 'log_dump4'
go
sp_dropdevice 'full_backup'
go


*****************************************************************
*****************************************************************
Query analyzer's response:



(1 row(s) affected)

'Disk' device added.

(1 row(s) affected)

'Disk' device added.

(1 row(s) affected)

'Disk' device added.

(1 row(s) affected)

'Disk' device added.

(1 row(s) affected)

'Disk' device added.

(1 row(s) affected)

'Disk' device added.

(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)

Processed 2 pages for database 'mcbath', file 'mcbath_Log' on file 4.
BACKUP LOG successfully processed 2 pages in 0.130 seconds (0.102 MB/sec).

(1 row(s) affected)


(1 row(s) affected)

Processed 1 pages for database 'mcbath', file 'mcbath_Log' on file 4.
BACKUP LOG successfully processed 1 pages in 0.093 seconds (0.011 MB/sec).

(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)

Processed 1 pages for database 'mcbath', file 'mcbath_Log' on file 4.
BACKUP LOG successfully processed 1 pages in 0.089 seconds (0.023 MB/sec).

(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)

Processed 1 pages for database 'mcbath', file 'mcbath_Log' on file 4.
BACKUP LOG successfully processed 1 pages in 0.085 seconds (0.018 MB/sec).

(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)

Processed 1 pages for database 'mcbath', file 'mcbath_Log' on file 4.
BACKUP LOG successfully processed 1 pages in 0.109 seconds (0.014 MB/sec).

(1 row(s) affected)


(19 row(s) affected)

Server: Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

(0 row(s) affected)

Device dropped.
Device dropped.
Device dropped.
Device dropped.
Device dropped.
Device dropped.

0
Comment
Question by:Dstathakis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
16 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12118780
You appear to be trying to backup a database that's still online without specifying REPLACE. If you want to overwrite it, try:

restore database mcbath from full_backup with norecovery, REPLACE
0
 

Author Comment

by:Dstathakis
ID: 12118830
The backups are (I believe) successfully happening. It is the RECOVERY that is failing.  

(From the response section)

****************************
BACKUP STUFF seems to be ok.
****************************
Processed 2 pages for database 'mcbath', file 'mcbath_Log' on file 4.
BACKUP LOG successfully processed 2 pages in 0.130 seconds (0.102 MB/sec).

****************************
RESTORE STUFF seems to be PROBLEM.
****************************

Server: Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12118926
Right... the recovery stuff uses the RESTORE command, you haven't dropped or detached the database when you run the first RESTORE command, but you're trying to restore over an active database without telling it to replace what's there.

You either have to detach or drop the database, or you have to use the optional argument to tell the RESTORE to REPLACE what's there.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:Dstathakis
ID: 12119171
**********************************************
Attempt to detach - with results
**********************************************

sp_detach_db 'mcbath', 'true'

Server: Msg 3701, Level 16, State 3, Line 1
Cannot detach the database 'mcbath' because it is currently in use.

**********************************************
Attempt to restore using REPLACE - with results
**********************************************

restore database mcbath from full_backup with norecovery, REPLACE
go

Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


I'm sorry, but, I must be miscommunicating.  Please bear in mind that I'm running the script IN Query Analyzer.  As you can see by the code I've included, whether I attempt to detach, or recover with replace either way, I'm still hammered with the exlusive problem.  I do realize that I could use EM and detach the database; however, I'm NOT able to do this with T-SQL.  I receive an error message as I've included above.  Maybe I'm presenting an anomoly here.  Perhaps, you can't do it from query manager because query manager inherintly attaches and to try to detach (kind of like why we don't die in our dreams)
0
 
LVL 39

Accepted Solution

by:
appari earned 1000 total points
ID: 12119207

try after adding the following just before the first restore command.

use master
go
0
 
LVL 15

Assisted Solution

by:jdlambert1
jdlambert1 earned 1000 total points
ID: 12119233
I suspect Query Analyzer is connected to the database your trying to run this against. Manually set the connection to the master database or add this line at the start:

USE master

If that isn't sufficient, check sp_who to see what/who is still connected.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12119239
Oops. Sorry appari, didn't mean to repeat you. I was researching a little more before I hit submit, and didn't see your post.
0
 
LVL 39

Expert Comment

by:appari
ID: 12119471

lol:) i think atleast a point split was a better option.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12124104
I don't mind a bit, Dstathakis. He did suggest "Use master" first. JD
0
 

Author Comment

by:Dstathakis
ID: 12129272
Since they don't mind, I don't mind.  Whatever is equitable.  I do appreciate jdlambert1 working through the problem with me.

0
 
LVL 39

Expert Comment

by:appari
ID: 12365164

the question was reopened to split points, see previous comments from  RomMod
0
 

Expert Comment

by:RomMod
ID: 12395779
I believe the intention was to split between Experts appari and jdlambert1. I have closed the question accordingly. Thanks, All!

RomMod
Community Support Moderator
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question