Post reply

logfile lost on database detach.. help!

  • July 13, 2011 at 10:36 am

    #239605

    Situation:

    1. Database detached

    2. Hardware failure

    3. Logfile for database lost

    4. Cannot re-attach database because of missing logfile

    Error Text: The log scan number (472629:408:1) passed to log scan in database is not valid....etc

    5. No backup to restore from.

    How can I reattach this file and re-initialize the log file?

    Help!

  • July 13, 2011 at 10:44 am

    #1352294

    do you have a .bak you can restore to ? .... Nevermind, just saw #5.

  • July 13, 2011 at 10:52 am

    #1352300

    is this a dev environment ? Are you using VMWare ? if so, you could restore a snapshot if you have one & the current data is not critical

    look at this link, also.

    http://social.msdn.microsoft.com/forums/en-US/sqldisasterrecovery/thread/189be01f-23de-48b0-96cc-8f1292c13c54/

  • July 13, 2011 at 10:58 am

    #1352309

    I'll take a look at the link, Bob, thanks. This is on a production server that crashed when the raid controller blew up and not VMWare either..

    Basically the worst of all possible scenarios..

  • July 13, 2011 at 11:03 am

    #1352313

    I tried re-attaching without the log file so that it would generate a new logfile but I get the same error "The log scan number.....is not valid."

  • July 13, 2011 at 1:58 pm

    #1352436

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

    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

  • July 13, 2011 at 2:10 pm

    #1352448

    Thanks, Gail. I'm reading up on it now.

  • July 13, 2011 at 2:25 pm

    #1352462

    Gail, followed your steps to the letter..

    created a database NOT the same size as I don't have a spare 600 gigs on the drive at the moment.

    deleted the files

    swapped in the mdf file

    set it in emergency mode

    set it to single_user

    ran dbcc checkdb (mydatabase, repair_allow_data_loss) and this is the error I get

    File activation failure. The physical file name "D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\databaselog.ldf" may be incorrect.

    The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

    Msg 5028, Level 16, State 4, Line 15

    The system could not activate enough of the database to rebuild the log.

    DBCC results for 'database'.

    CHECKDB found 0 allocation errors and 0 consistency errors in database 'database'.

    Msg 7909, Level 20, State 1, Line 15

    The emergency-mode repair failed.You must restore from backup.

    I'm guessing that last bit is absolute? I MUST restore from a non-existent backup?

  • July 13, 2011 at 2:29 pm

    #1352466

    Yup. That's an absolute. Emergency mode repair is the very last resort. It failed.

    Restore from backup or declare the database a complete loss and start from scratch.

    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

  • July 13, 2011 at 2:30 pm

    #1352467

    woohoo.. I got it down to THESE errors

    Msg 5028, Level 16, State 4, Line 15

    The system could not activate enough of the database to rebuild the log.

    DBCC results for 'database'.

    CHECKDB found 0 allocation errors and 0 consistency errors in database 'pakistan'.

    Msg 7909, Level 20, State 1, Line 15

    The emergency-mode repair failed.You must restore from backup.

  • July 13, 2011 at 2:38 pm

    #1352473

    sigh.. gonna take a long time to rebuild but lesson learned.

    Thanks, Gail.

  • July 13, 2011 at 2:46 pm

    #1352479

    See if you can access any of the DB while it's in emergency mode. Maybe....

    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

  • July 13, 2011 at 3:37 pm

    #1352504

    No luck on that, Gail.

    I can't even access the schema. I get an error of:

    Could not continue scan with NOLOCK due to data movement.

  • July 13, 2011 at 3:51 pm

    #1352511

    See previous reply then. Complete loss.

    Are you sure there are no backups anywhere? No backups taken for dev purposes or anything similar?

    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

  • July 13, 2011 at 4:22 pm

    #1352527

    Absolutely. Complete loss.

Viewing 15 posts - 1 through 14 (of 14 total)

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