March 07, 2013

Recover/Repair a suspected database in SQL Server 2000 using Query

Some of we are using SQL server 2000 in our offices for database creation. In SQL server 2005 when a database is suspected we can easily recover or restore it with a query
 "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 the database 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

Related Posts Plugin for WordPress, Blogger...
Related Posts Plugin for WordPress, Blogger...