Recover corrupted mdf file - SS 2000

  • I have just taken over responsiblity of several new database projects and the first task given was to recover some SS 2000 databases that are corrupted. THere are no backups (which is another story all together)....I only have the mdf and ldf.

    I have attempted the following without success:

    1) Tried to attach the mdf - received Error 9004: An error occurred while processing the log for database

    2) Tried using SP_ATTACH_SINGLE_FILE_DB to have the log file rebuilt but was not successfull either.

    Any other suggestions for recovering the databases if I don't have the full backup?

    Dave Coats

  • Mentioned in the other other posting you added to - if no SQL backups check for a software backup of the file system or a Windows Shadow copy backup. If there is you could attach the mdf.

  • Yes...that's how we actually got the mdf and ldf. The server went down and they were able to retrieve the files from the server backups, we just didn't have any database backups. So then I proceeded to stand up a new instance of SQL Server 2000 and attach the databases. However, I have not been able to attach or recover them due to corruption.

    Dave Coats

  • You may be able to hack the DB back into the server. See this blog post. It's for 2005/2008, but the procedure's the same on 2000 up until the point where you set the DB to emergency.

    See if you can follow it and get the DB back into the server. If you can, post back and I'll help you from there.

    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
  • Ok...I think I'm not so lucky. I've followed your blog post and have gotten the database into an Emergency State on the server. When I try to view the tables, views, etc under Ent Mgr it shows no items.

    I then go to Query Analyzer object browser and try to view tables from there and receive this error:

    "I/O error (bad page ID) detected during read at offset"

    From internet research, it appears to be a bad corruption.

    Any ideas? Thanks for your help so far.

    Dave Coats

  • What did you do to get it into emergency mode? the ALTER DATABASE described on the blog is only for SQL 2005+, it won't work on SQL 2000.

    Basically, what are the precise steps that you've done up until now?

    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
  • I was not able to attach the database at all. So I created a blank database with the same name, stopped the service, replaced the blank mdf with my corrupted mdf, restarted the service and it came up in "Suspect\Emergency". Then I updated the sysdatabases status field in master to be 32768. Restarted the service and it was then in "Emergency" mode.

    At this point I can run select * from sysobjects and it returns 32 objects and then gives this message:

    "Server: msg 823, Level 24, State 2, Line 1

    I/O error (bad page ID) detected during read at offset..."

    Should I continue with "Step 2: Emergency Mode Repair" from your blog? Thanks again for your help!!

    Dave Coats

  • Before you try a repair, let's see how bad it is.

    DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

    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
  • Returns following message....guess its bad then????

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:219) with latch type SH. sysobjects failed.

    Dave Coats

  • Not repairable. Sorry, DB's a loss.

    You may be able to extract some data with it in emergency mode. Some objects will likely fail to script, some will fail to export. It's the best that you're going to be able to do 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
  • sparky-407434 (6/18/2010)


    DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS

    No point. If checkDB can't complete a check (in this case because the system tables are damaged) there's no way it's going to be able to repair it.

    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,

    Ok...I was afraid that was the case. Thanks for your help with this...I really appreciate it. I have 3 other dbs that I need to attempt to recover, if I can get any of them to do a CHECKDB, I'll repost. Thanks again!

    Dave Coats

Viewing 13 posts - 1 through 12 (of 12 total)

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