suspect database after san failure

  • Gail Shaw

    SSC Guru

    Points: 1004424

    I asked about the upgrade message. Is the SQL instance that you're trying to recover this with IDENTICAL to the version that this DB was attached to before the SAN failure?

    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
  • Sunil Padarath

    SSC Eights!

    Points: 852

    on the same server we have 2 instances of the sql server. not to muck up the production file (which is in suspect mode) i copied the mdf and ldf file to the test instance and working from there. they both are on the same server and same version

    Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

    Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

  • Sunil Padarath

    SSC Eights!

    Points: 852

    yes they are identical

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Ok, so the DB is attached but suspect. Please run the following.

    ALTER DATABASE <database name> SET EMERGENCY

    Once you've done that, you should be able to access the DB, though it will be restricted to single user. Please confirm once you've run that, that you can access the database and you can run a simple query (just query one of the system views, like sys.objects)

    If you get any errors, post the complete messages 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
  • Chad Crawford

    SSChampion

    Points: 11605

    oh my. This is just a comment from someone just watching, but Sunil, you are in a bad spot. Usually something of this magnitude cannot be fixed - it isn't recoverable and restoring a backup is the best solution. Since you don't have one you would be really stuck. But I will say that Gail is not one of your every-day, run-of-the-mill SQL users. You are in the hands of one of the best SQL surgeons I have ever seen, and if you follow Gail's instructions precisely you have the best shot of getting your database (or at least part of it) back. I hope it goes well.

    Chad

  • Sunil Padarath

    SSC Eights!

    Points: 852

    Thanks Gail

    I get this error when i run set to emergency

    Msg 602, Level 21, State 33, Line 1

    Could not find an entry for table or index with partition ID 327680 in database 17. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.

  • Sunil Padarath

    SSC Eights!

    Points: 852

    hi Gail

    Please let me know when u online

  • Sunil Padarath

    SSC Eights!

    Points: 852

    Hi Gail

    I have tried all the steps you had requested in the live system but still get the same error when trying to set mode to emergency

    Msg 602, Level 21, State 33, Line 1

    Could not find an entry for table or index with partition ID 327680 in database 22. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.

  • Fal

    SSCrazy

    Points: 2871

    Hi Sunil,

    I'd suggest that Gail is otherwise busy right now, so give her some time to get back to you.

    While you wait, you should start banging some heads together to get backups in place. This event should scare the necessary authorities at your company into taking action on this. At the very least, you need to get backups happening onto other disks (ie: NOT the same disks/SAN as those the db files live on.) Use lots of 1.44MB floppies if you must.

    Steve.

  • Sunil Padarath

    SSC Eights!

    Points: 852

    Thanks Steve

    We have been doing regular backups and have manage to get back all DB's online except for this one. Its a newly created DB (7 months) and somehow we forget to include this in the backup job. We are certainly going to tighten up on this area.

  • Sunil Padarath

    SSC Eights!

    Points: 852

    Hi Gail

    I am going out of office for now for (2 hours).

  • Sunil Padarath

    SSC Eights!

    Points: 852

    Hi Gail

    I am in office now

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Sunil Padarath (5/13/2010)


    Thanks Gail

    I get this error when i run set to emergency

    Msg 602, Level 21, State 33, Line 1

    Could not find an entry for table or index with partition ID 327680 in database 17. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.

    Game over.

    Emergency mode is the absolute last resort. If it fails, there's nothing more that can be done. This database is toast, it's not recoverable, it's not coming back.

    I hope this is not critical for your business.

    There are 3rd party tools that are available that claim to be able to extract data from damaged database files. Personally, I've not had any success doing so when I tried them on databases too damaged to repair. It may be worth trying out a demo, see if anything at all can be extracted. Personally I suspect the chances are slim-to-none at best 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
  • Gail Shaw

    SSC Guru

    Points: 1004424

    Fal (5/13/2010)


    I'd suggest that Gail is otherwise busy right now, so give her some time to get back to you.

    Sleeping actually. I'm in one of the european time zones, so those mails came in at 10pm, 1am and 1:30 am.

    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
  • Sunil Padarath

    SSC Eights!

    Points: 852

    Thank you Gail.

Viewing 15 posts - 16 through 30 (of 36 total)

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