i got same prob few days ago. the below failed to restore in my case but it is said that its success is about 90%
-- There are 3 levels of repair that can be done.
-- You try step 1 = REPAIR_FAST first, if it does not work (db is still suspect in Enterprice Manager)
-- then you should stop service and start service again before continue with next step.
-- Next step 2 = REPAIR_REBUILD. If that does not work (still suspect) then you stop and start service.
-- Do NOT skip the stop and start service step between each time you try a higher level of repair. SQL needs to be reset.
-- Step 3 if the other 2 do not work is REPAIR_ALLOW_DATA_LOSS. There has been very few times when this one has not been sufficient.
-- It is normally not actual data that dissapears if REPAIR_ALLOW_DATA_LOSS can fix the problem but the other steps cannot
-- but rather primary keys, foreign keys, default constraints or indexes. There is no guarantee though.
-- This is step 1 - run this in master in query analyser and replace 'ips' with your database name
-- Note: this is name as seen in Enterprice Manager and not physical file name
dbcc checkdb ('ips',REPAIR_FAST)
-- This is step 2 - Do the same as step 1 (don't forget to stop and start service before)
dbcc checkdb ('ips',REPAIR_REBUILD)
-- Now starts step 3. Actual data CAN be lost with this command but it usually doesn't happen. Make sure you have copy
-- of the physical files (BOTH .mdf and .ldf!) before you run it. To only have copy of .mdf is sometimes enough, sometimes not, have BOTH!
-- To be able to run level 3, database must be in single user mode
-- Use this command in query analyser to set db as Single User mode. You should use it Directly after you have stopped and restarted
-- the SQL service so that the query analyser session you execute it in is the only user.
sp_dboption 'ips', 'single user', 'TRUE'
-- Step 3 - replace name the same as step 1
dbcc checkdb ('ips',REPAIR_ALLOW_DATA_LOSS)
-- If the repair works, then you can set back single user mode with this command
-- You can also go to database properties in Enterprice Manager
sp_dboption 'ips', 'single user', 'false'
-- resets the suspect status bit. Normally doesn't work but can always try. This command forces the suspect bit to off
-- stop and start service after and look in enterprice manager. If SQL cannot read database, then it willl set back
-- the suspect bit automatically.
exec sp_resetstatus 'ips'
-- Some more misc scripts that can be useful
EXEC sp_detach_db 'inbound_mike', 'true' -- true and false apply to option 'skipchecks', loses replication!!!
EXEC sp_detach_db 'inbound', 'true' -- true and false apply to option 'skipchecks', loses replication!!!
-- after attach, users in that database will be un-usable. Run sp_dropuser 'ips' after and go into Enterprise Manager and re-create.