Connect database problem:I/O error (bad page ID) detected during read

[fmientjes]

I am trying to get a mdf and ldf file to run again.
This is about my last chance to get it right (no good backups...)
First I tried to make a new databse and point to the mdf file: "this is not a valid file" SQL server says.

Then I made a new database with the same name, stopped SQL server, deleted the new files and put the "old" files in.
Start SQL server.
At first everything looks OK, but when going to tables or views it comes up with: "Database '200' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details."

running dbcc checkdb ('200') in the query analyzer gives the same message.

In the log files I see: "I/O error (bad page ID) detected during read at ...200.mdf"


Can someone help me with how to get the database back to life? Say Yes please.
Frans

 

[Catadmin]

Have you tried a regular Restore Database via the Enterprise Manager interface yet?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[cyno]

Did you run the DBCC checktable on all the tables and see if there is any index corruption

 

[fmientjes]

A normal restore doesn't work ("device error or device off-line")
dbcc checktable only works with online databases, but that is exactly the problem: I cannot get it online.

I have tried to put the databse into emergency mode, but still I cannot get to the tables.

Please, are there any more suggestions????

 

[Catadmin]

Looking at your error again, it contains the phrase " or insufficient memory or disk space"

Have you verified your Hard Drive isn't full or that the remaining free space is more than the size of your .mdf and .ldf files?

Are you trying to restore via command line tools, T-SQL or Enterprise Manager? Or have you tried all three? Database Off-line sounds like a DB that has had the main .Bak file restored but is waiting for Trans Logs to be restored. Have you tried A) right clicking the DB in Ent Manager to see if it gives you an Online option or B) Restoring the DB with the With Recovery option?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[fmientjes]

I have checked the hard drive and no prbolem for size.
I have used Enterprise manager and Query analyzer to restore the database. No command line tools (I am not familiar with that yet).
Right clcicking in EM doesn't give me the options you described. The database is marked suspect and I can only choose for detach or take off line.

The biggest problem is that using dbcc checkdb it gives me the previous mentioned error message. Is it so thet the wrong page id is on a very bad spot?

If you still have suggestions, please let me know.
Frans

 

[Catadmin]

If the database is marked suspect, then it may be corrupted.

However, if you look up Suspect Database in Books Online, it lists a method of over-riding and restoring a suspect DB. I've never tried it myself.

Make copies of your .mdf and .ldf files via Windows Explorer before you try this. From the C.Y.A. department of I.T. practices. @=)

Let us know if that works for you or not.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[JayKusch]

Check out my FAQ on this subject ... faq962-4210

Thanks

J. Kusch