Attach MDF that wasn't cleanly shutdown

  • Hi experts, im trying to recreate a disaster recovery scenario and simulate a drive going down that hosts the database log (ldf) file. I want to see / document the steps I'd need to perform to recover the database from the mdf file (if at all possible). The steps i've performed so far are:

    Create a new database 'Test'

    mdf file in default SQL location

    ldf file on a different drive (happens to be a removable pen drive)

    I then remove the pen drive to simulate a drive going down. So now the Test database is inaccessable right.

    So what I need to know is:

    A. Is it possible to recover this database from the remaining mdf file (assuming no full backup exists)

    B. If so, how?

    Thanks in advance

  • NickBalaam (8/6/2013)


    A. Is it possible to recover this database from the remaining mdf file (assuming no full backup exists)

    Maybe.

    B. If so, how?

    http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

    However if you're working on a DR plan, a far better use of your time would be in implementing a backup strategy as the appropriate recovery procedure from the scenario you describe is restore from backup.

    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
  • Thanks for that Gail. I'll check that post now.

    Yes I totally agree RE: DR plan, we do have a proper DR plan for our prod/dev servers (inc full db and hourly log backups etc), this is really for my own personal learning on my machine to see if it "can" be done. I can't forsee a circumstance where I'd need to do this in live. 😀

  • Well I tried that (from the blog post), fascinating. First method fine, stop service and delete log file etc. Second method (detach - delete - create new database and rename files) leaves the database stuck in recovery_pending mode even if I issue a [set emergency] stmt? So can't run the checkdb repair and so can't rebuild the log on the fly. Moral of the story....backups!! Just as well it's a lab exercise :w00t:

  • Can happen. Emergency mode is a last resort, it's not guaranteed to work in all cases.

    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

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

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