• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

How do I run a recovery from query analyzer

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
Dstathakis
Asked:
Dstathakis
  • 5
  • 3
  • 3
  • +1
2 Solutions
 
jdlambert1Commented:
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
 
DstathakisAuthor Commented:
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
 
jdlambert1Commented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
DstathakisAuthor Commented:
**********************************************
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
 
appariCommented:

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

use master
go
0
 
jdlambert1Commented:
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
 
jdlambert1Commented:
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
 
appariCommented:

lol:) i think atleast a point split was a better option.
0
 
jdlambert1Commented:
I don't mind a bit, Dstathakis. He did suggest "Use master" first. JD
0
 
DstathakisAuthor Commented:
Since they don't mind, I don't mind.  Whatever is equitable.  I do appreciate jdlambert1 working through the problem with me.

0
 
appariCommented:

the question was reopened to split points, see previous comments from  RomMod
0
 
RomModCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now