Recover the Corrupted database in SQL server 2000

Dear All
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’)
use mscit_era3
alter database mscit_era3 set SINGLE_USER
dbcc checkdb (‘mscit_era3’, repair_allow_data_loss)
use master
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’.

Prashant Deshpande


One comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s