Today, I was "lucky" to deal with a suspect database on a Sql Server 2000 SP4. Always a little bit targeting to get it solved, isn't it?
This time, the database would not recover because of a torn-page detection in the data-file. After having taken an off-line backup (on-line backup on a suspect database does not work), I tried a lot of statement(-combinations):
starting with sp_resetstatus, dbcc checkdb() including the one with repair_allow_data_loss, attachdb (with and with-out ldf-file), update sysdatabases ...
Pushing my luck with dbcc rebuild_log, which rebuilds the ldf-file, hoping I could pass the recovery phase and copy data to another database. However, no luck ...
Finally, I got the golden tip from Microsoft (I have to admit I pushed the guy to the ultimate :-) because I would not hang in the telephone without another atempt to get the data back, so, thank you Lars!) : emergency mode for a database. You should not run this on your regular databases! Take a look in BOL under topic "sysdatabases", column "status".
With the database in emergency mode, I was able to get the data from 156 of the 158 tables save in another database. For the 2 remaining tables most data could be saved, except for the few rows being on the torn-pages (I gues 2 pages, 1 on a datapage, another on an page with indexes).
Lucky client, happy me !