Cannot attach a database that was being restored.

Question

Cannot attach a database that was being restored.

KATHLEEN Y ZHANG

Hall of Fame

Points: 3534
More actions
April 13, 2007 at 8:37 am

#172980

I am trying to restore a database from copies of data file and log file from a database thats in log shipping.
When I run:
EXEC
sp_attach_db @dbname = N'partner_dirty',
@filename1
= N'd:\s2k5_one\data_dirty_one\partner_data.mdf',
@filename2
= N'd:\s2k5_one\data_dirty_one\partner_log.ldf'
GO
I got the error message:
Msg 1824, Level 16, State 1, Line 1
Cannot attach a database that was being restored.
I was able to restore databases this way in sql 2000. Is there a way to get around in 2005?
Thanks!

Kathleen

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Was the database in read only before doing this? If so you cannot do it as the server cannot load sever related data into the database at the same time as it cannot write to it.

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 2

Yes, the database is in log shipping standby mode. I did it all the time in sql 2000 by first shutting down sql server, then copy the data file and log file over, then restore it on another server using

sp_attach_db, the database will be brought up online as read-write db.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Right the server can be in standby, what I am concerned with is the actual DB was marked as read-only. Sp_Attach_DB cannot change that when loading and will error as you describe.

Andrew Ryan SSC Veteran Points: 272 More actions · Answer 4

I have had many snaps taken from a read only databases and attached elsewhere just fine. I am now receiving this error also. I am going from 2000 to 2005 and this is not the first time I have done this from 2000 to 2005.

I am trying to replicate a database to test systems from a read only log shipping copy (as I have many times in the past.) Now, I am receiving the "Cannot attach...restored" error.

EDIT: I go and post this and then I have a thought. I think this is the first time we have gone from MS SQL 2000 directly to MS SQL 2005. The other times we had an intermediate step of attaching to an MS SQL 2000 database for a collation correction. To be continued...

Second EDIT: The problem was going directly from MS SQL 2000 to MS SQL 2005. We attached the Database to another MS SQL 2000 then to the MS SQL 2005 and it solved the issue. This database is 700GB in size so breaking log shipping and performing a full backup and restore is not a palpable option.

"From the smallest necessity to the highest religious abstraction, from the wheel to the skyscraper, everything we are and everything we have comes from one attribute of man - the function of his reasoning mind.

Paul White SSC Guru Points: 150467 More actions · Answer 5

You cannot usually* attach a database that was detached while in STANDBY.

The process of placing a database into STANDBY writes to an file that you specify. The file contains an pre-image of pages changed by the UNDO phase of recovery. That phase undoes the effects of uncommitted transactions. That file is not available to the attach routine, so it cannot recover the database to a consistent state.

I agree it seems dumb that you can detach a database in a state which prevents it from being re-attached, but there you go.

Paul

* = You can if no UNDO operations were necessary to place the database in STANDBY. The file written by the change to STANDBY would be essentially empty in this case (although 32K in size).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Patrick Wathey SSC Veteran Points: 293 More actions · Answer 6

Move all the mdf, ndf, ldf to temp sub directories on your the server you're trying to attach the db to
Create a new empty database with the same name as the one you are trying to attach - and all the extra files in the right places (mdf, ndf, ldf etc) with right filenames - you can script this from the original db or use GUI, but make the size very small - 5Mb
Take the database Offline.
Copy your original MDF, NDF and LDF files and overwrite the newly created files.
Bring the database back Online.

saved me a lot of pain

inanbd Grasshopper Points: 12 More actions · Answer 7

Patrick Wathey wrote:

Move all the mdf, ndf, ldf to temp sub directories on your the server you're trying to attach the db to
Create a new empty database with the same name as the one you are trying to attach - and all the extra files in the right places (mdf, ndf, ldf etc) with right filenames - you can script this from the original db or use GUI, but make the size very small - 5Mb
Take the database Offline.
Copy your original MDF, NDF and LDF files and overwrite the newly created files.
Bring the database back Online.
saved me a lot of pain

Thanks. Worked for me.

jamesmcarrasco Newbie Points: 2 More actions · Answer 8

Patrick Wathey wrote:

Move all the mdf, ndf, ldf to temp sub directories on your the server you're trying to attach the db to
Create a new empty database with the same name as the one you are trying to attach - and all the extra files in the right places (mdf, ndf, ldf etc) with right filenames - you can script this from the original db or use GUI, but make the size very small - 5Mb
Take the database Offline.
Copy your original MDF, NDF and LDF files and overwrite the newly created files.
Bring the database back Online.
saved me a lot of pain

It was good for me to read this information, I didn't know about it, thank you.