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

Thanks
Prashant Deshpande

Advertisements

1 Comment

Filed under Database Server, SQL Server

One response to “Recover the Corrupted database in SQL server 2000

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s