Cannot attach a database that was being restored.

  • KATHLEEN Y ZHANG

    Hall of Fame

    Points: 3534

    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

  • Antares686

    SSC Guru

    Points: 125444

    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

    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

    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

    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: 150442

    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).

  • Patrick Wathey

    SSC Veteran

    Points: 293

    1. Move all the mdf, ndf, ldf to temp sub directories on your the server you're trying to attach the db to
    2. 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
    3. Take the database Offline.
    4. Copy your original MDF, NDF and LDF files and overwrite the newly created files.
    5. Bring the database back Online.
    saved me a lot of pain

Viewing 7 posts - 1 through 7 (of 7 total)

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