"EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(‘yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER"But while using SQL server 2000 this query was not properly working. Most of thedatabase in SQL server 2000 is due to the corruption of log file. The following is an easy way for recovering/repairing a sql server 2000 suspected database.This is 5 Step process
Step 1 : This is for replacing dbname/ldf path name
Run the Query using Query Analyser (QA)
Use master
Go
sp_configure 'allow updates', 1
Reconfigure with override
Go
Step 2: execute the following script
Update sysdatabases set status= 32768 where name = 'your db name'
Step 3:
Restart MSSQLSERVER service, the database will be in Emergency mode
Step 4: Rebuild Log. From QA execute script
DBCC REBUILD_LOG ('your db name', 'db path log name{eg: E:\postman_Log.LDF}')
after running this -You got a
--Message - Warning: The log for database 'postman' has been rebuilt.
Step 5 From QA execute following script
Use master
Go
sp_configure 'allow updates', 0
AFter this your database is recovered but the same is in single user mode. Please go to properties and set it to multi user mode , or use the query
ALTER DATABASE yourDBname SET MULTI_USER
courtsey.saparavur
