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