Solved

How do I run a recovery from query analyzer

Posted on 2004-09-21
16
505 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
  • 5
  • 3
  • 3
  • +1
16 Comments
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Dstathakis
Comment Utility
**********************************************
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 250 total points
Comment Utility

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

use master
go
0
 
LVL 15

Assisted Solution

by:jdlambert1
jdlambert1 earned 250 total points
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
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
Comment Utility

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

Expert Comment

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

Author Comment

by:Dstathakis
Comment Utility
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
Comment Utility

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

Expert Comment

by:RomMod
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now