how to shoot SQL error 823?

Question

I had a laptop die HDD went dead which a running sql database was opened (in program) I had to have the hard drive recovered however all i got back was a bad database which has the torn page. All pro software seams to be able to read my database and fix if i pay the 399.00 for the software however i was hoping i could use the built in DBCC However it seams everything i try wont work so i am hoping for some help. Here is the error when I try to bring the database online

Msg 945, Level 14, State 2, Line 1 Database 'CompassDefaultDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed. Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x20203e65; actual: 0x1f329010). It occurred during a read of page (1:9) in database ID 5 at offset 0x00000000012000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\CompassDefaultDB_51120120410_data.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

So Then I Try

DBCC CHECKDB (CompassDefaultDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

That give me

Msg 945, Level 14, State 2, Line 1 Database 'CompassDefaultDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

So Then I Try

ALTER DATABASE CompassDefaultDB SET EMERGENCY;

Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x20203e65; actual: 0x1f329010). It occurred during a read of page (1:9) in database ID 5 at offset 0x00000000012000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\CompassDefaultDB_51120120410_data.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Then

DBCC CHECKDB (CompassDefaultDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

Msg 922, Level 14, State 1, Line 1 Database 'CompassDefaultDB' is being recovered. Waiting until recovery is finished.

It stays in this mode forever and does not seam to finish this database is only 2MB with like 17 Rows of data i am after. I can restart the sql server and repeat the same steps with the same results. Is there something i am doing wrong? any help is greatly appricated

I do have the origional corrupted database and have tried to restore and repeat but with the same results I have also tried to bring up a database with the same name delete the mdf file and replace with me mdf file with the same basic results. There is no backup :( Thanks again for looking

Answer 1

Attempting to repair the database isn't going to help. Page 1:9 is a system object and system objects can't be repaired. You'll want to script out the tables and create a new database. Then create the tables in the new database, and copy over the data into the new database.

If the database won't open you can try setting it to emergency mode using ALTER DATABASE MyDB SET EMERGENCY which may bring the database online. If you does you can script the data out. If not you're basically screwed.

If you had a backup... Another option would be to restore the database to a point in time before the page became corrupt. If you have a backup from that far back you can restore that backup, then roll the transactions logs forward and you should be able to get back to a state where you have all the current data without the corruption.