recover broken msde 2000 8.0 database

Question

i have a msde (mssql) 2000 / v8.0 database that has one corrupted table. Microsoft SQL Server Management Studio Express can see the database STRUCTURE, but not the data. i can see the DATA of the tables using "SysTools MDF Viewer 1.0" (working link), but that's just for viewing, not for recovering the data.

the databases are corrupt because of harddrive failure (i know, backup backup.. i'm focusing on getting all the data back for now :( )

UPDATE 1: tried the following query:

CREATE DATABASE NewDbName
      ON(NAME=’OldDbName,
            FILENAME='C:\my\path\to\file.mdf')
      LOG ON(NAME='mydatabase_Log',
            FILENAME='C:\my\path\to\file.ldf')
      FOR ATTACH

and got the following result :

Server: Msg 823, Level 24, State 2, Line 1
   I/O error (bad page ID) detected during read at offset 0x00000001354000 in file 'C:\my\path\to\file.mdf'.

Connection Broken

currently, i'm trying to investigate this error msg (with little progress), mainly reading here. haven't tried all that yet

i was wondering if i can't construct the db from the transactional history, but i don't have "a good backup" to transaction from. is it going to work if i try that method using a "empty db" (with just the structure)

really big thanks for giving me suggestions on this! highly appreciated!

Update 2: Ran all the (updated) steps and i got to the data (YAY!), i'm seeing it in the SQL query analyzer, i just need to figure out how to export it safely and i'll be able to breathe! THANK YOU VERY MUCH!

Update 3: following this recover guide

Update 4: (epilogue) First and foremost, thank you Craig for the awesome help, your input was invaluable in solving this crisis.

After following steps 1-4 (with no error messages) i got stuck at DBCC CHECKDB. The process was taking more than 6 hours to complete. With no IO/CPU activity, i decided to stop the operation as it seemed to hang.

After checking with the client, it was revealed that straight data backup is desirable (as triggers were recoverable by other means ), so i proceeded with Step 5a and copied the data (using SELECT * INTO mynedatabase..sometable FROM mydatabase..sometable.) to a sane (new and empty) database. From there on all utilities (such as export / backup ) worked as expected.

for backup i used (writing here for reference)

BACKUP DATABASE backup_db TO DISK = 'c:\data_files\working.dat'

---

this should serve as (yet another) reminder: backup EVERYTHING

Answer 1

The transaction log file can't make up for a corrupted database file. Its just going to try and rollback or roll forward transactions on the database file as it is recovered.

One more possible thing to try although I doubt it will work. Even if it does, with a corrupt mdf, you're probably not going to get back everything you need.

As a preliminary step, and being sure that you have a copy of your mdf and ldf files somewhere safe, remove any entries of the original database

DROP DATABASE mydatabase

1. Create a new database

Use the the same physical file names and logical file names as was defined by the original corrupt database

--create the database
CREATE DATABASE mydatabase
  ON(NAME=’mydatabase,
        FILENAME='C:\my\path\to\file.mdf')
  LOG ON(NAME='mydatabase_Log',
        FILENAME='C:\my\path\to\file.ldf')

GO

2. Shutdown MSSQL

SHUTDOWN

Now copy in the corrupt mdf and ldf files over the newly created ones

3. Start MSSQL

You should now see the database in suspect mode

4. Put database into emergency mode SQL Server 2000

USE master
GO
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

UPDATE sysdatabases SET status= 32768 WHERE name = 'mydatabase'

5. restart MSSQL

5a. (optional) Since you're data is now accessible via Query Analyzer, you could just export your important data into a new database.

-- create the new database
CREATE DATABASE mynewdatabase

-- import data from the old database into the new
SELECT * INTO mynedatabase..sometable FROM mydatabase..sometable.

This won't help though with your triggers and metadata, but at least you'd have your data accessible in case the repair fails

6. Repair the database

This is the method I've used for repairing the database once its in emergency mode.

USE mydatabase
GO 

-- put database into single user mode
sp_dboption 'mydatabase', 'single_user', 'true'
GO 

-- repair the database, please be sure you have an extra copy somewhere of
-- your two db files
DBCC CHECKDB ('mydatabase' repair_allow_data_loss)
GO 

-- turn off emergency mode
USE master
sp_configure 'allow updates', 1
UPDATE sysdatabases SET status= 0 WHERE name = 'mydatabase'
GO

-- check space allocation consistency
DBCC CHECKALLOC ('mydatabase')

-- verify db integrity
DBCC CHECKDB ('mydatabase')

-- take the database out of single user mode
sp_dboption 'mydatabase', 'single_user', 'false'
GO 

-- disallow updates to the system tables
USE master
GO 
sp_configure 'allow updates', 0
GO