Post reply

Detach / Attach when db is in NORECOVERY mode

  • May 20, 2009 at 9:56 am

    #204476

    Hi

    Due to a script error, I accidentally detached a database today when it was in NORECOVERY mode (ie receiving log ship transaction logs)

    When I tried to reattach the database the attach command failed saying that its not possible to attach files when they are in NONRECOVERY mode.

    I ended up restoring the db from backup to get it back, but does anyone know how I could have got the db back from the mdf and ldf files?

    Thanks in advance

    Perry

  • May 23, 2009 at 8:54 am

    #999332

    Never heard of that, but I suspect that there isn't anything you can do. A detach in that phase would seem to suggest you had the backup files and could restore again. Since an attach does a roll forward/back, that can't complete because of the status.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 23, 2009 at 9:59 am

    #999338

    If a database is detached while in the recovering or standby state, it cannot be reattached (baring some odd hacks that may or may not work)

    In fact, in SQL 2008, you cannot detach a recovering database.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 27, 2009 at 9:36 am

    #1000743

    If a database is detached while in the recovering or standby state, it cannot be reattached .


    - Pradyothana     http://www.msqlserver.com

  • May 27, 2009 at 3:42 pm

    #1001064

    GilaMonster (5/23/2009)

    If a database is detached while in the recovering or standby state, it cannot be reattached (baring some odd hacks that may or may not work)

    Pradyothana Shastry (5/27/2009)

    If a database is detached while in the recovering or standby state, it cannot be reattached .

    Is there an echo in here?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 27, 2009 at 4:36 pm

    #1001112

    If a database is detached while in the recovering or standby state, it cannot be reattached.

    Hope that helps.

    I can haz more posting pointz?

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 6 posts - 1 through 5 (of 5 total)

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