Today I got one SQL server 2000 database which is in Suspect mode so tried to attach it in my local server but getting error and not able to attach so tried following options to recover it
1) created one new database with same name
2) Stopped SQL server service
3) Replaced corrupted mdf and ldf with newly created database.
4) Started SQL server service
5) shown that database status as SUSPECT
6) Then opened QUERY ANALYSER and executed following commands one by one.
select status from sysdatabases where name = ‘mscit_era3’
update sysdatabases set status= 32768 where name = ‘mscit_era3’
sp_configure ‘allow updates’, 1
reconfigure with override
dbcc rebuild_log (‘mscit_era3’, ‘d:\mscit_era3_log.ldf’)
alter database mscit_era3 set SINGLE_USER
dbcc checkdb (‘mscit_era3’, repair_allow_data_loss)
update sysdatabases set status= 0 where name = ‘mscit_era3’
DBCC CHECKALLOC (‘mscit_era3’)
dbcc checkdb (‘mscit_era3′)
alter database mscit_era3 set multi_user
Then lastly again restarted SQL server
and its done
Be sure the LDF file path is d:\mscit_era3_log.ldf’.