Dstathakis
asked on
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
go
sp_addumpdevice 'disk', 'log_dump1', 'd:\tmp\sql_backup\logdum1
go
sp_addumpdevice 'disk', 'log_dump2', 'd:\tmp\sql_backup\logdum2
go
sp_addumpdevice 'disk', 'log_dump3', 'd:\tmp\sql_backup\logdum3
go
sp_addumpdevice 'disk', 'log_dump4', 'd:\tmp\sql_backup\logdum4
go
sp_addumpdevice 'disk', 'full_backup', 'd:\tmp\sql_backup\full_ba
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.
"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
go
sp_addumpdevice 'disk', 'log_dump1', 'd:\tmp\sql_backup\logdum1
go
sp_addumpdevice 'disk', 'log_dump2', 'd:\tmp\sql_backup\logdum2
go
sp_addumpdevice 'disk', 'log_dump3', 'd:\tmp\sql_backup\logdum3
go
sp_addumpdevice 'disk', 'log_dump4', 'd:\tmp\sql_backup\logdum4
go
sp_addumpdevice 'disk', 'full_backup', 'd:\tmp\sql_backup\full_ba
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.
ASKER
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.
(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.
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.
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.
ASKER
**************************
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
lol:) i think atleast a point split was a better option.
I don't mind a bit, Dstathakis. He did suggest "Use master" first. JD
ASKER
Since they don't mind, I don't mind. Whatever is equitable. I do appreciate jdlambert1 working through the problem with me.
the question was reopened to split points, see previous comments from RomMod
I believe the intention was to split between Experts appari and jdlambert1. I have closed the question accordingly. Thanks, All!
RomMod
Community Support Moderator
RomMod
Community Support Moderator
restore database mcbath from full_backup with norecovery, REPLACE