RECOVER SUSPECTED DATABASE IN SQL 2000 REVISED

RECOVER SUSPECTED DATABASE IN SQL 2000 REVISED

Recover Suspected Databases In SQL 2000 Without Restoring 

The databases of SQL will be suspected whenever power shutdown of the server without proper shutdown. The log file of SQL database may be corrupted due to HDD error / improper shutdown of the PC.

Note : Never Restore the database of SQL 2000 for Suspect issue it may leads to data losses.

 Follow The Below Steps To Recover SQL 2000 Databases

All the attached Queries are should be executed in SQL Query Analyzer. change the DBNAME as actual database name of the suspected database.

1. Note down the database patch i.e MDF and LDF file path using below T-SQL Query

Select * from sysaltfiles

2. Allow SQL Server Settings by change the configuration using the below SQL Query 

EXEC sp_configure 'Allow updates', '1' Reconfigure with override

3. Change the database state as Emergency Mode.

UPDATE master.dbo.sysdatabases
SET Status = -32768
WHERE [Name] = 'DBNAME'
GO 

 4. After execute the database becomes read only and emergency mode. then change the database into Single User mode to rebuild log file of the database.

EXEC sp_dboption 'DBNAME', 'Single User','TRUE'

5.  Rebuild log file of the suspected database using the below T-SQL Query.

DBCC REBUILD_LOG (‘ABC’,’D:\Data\DBNAME.LDF’)

Note: The above path should not be same path of available log file of suspected database.
6.  Execute the following query to repair the database with minimum data loss

DBCC CHECKDB('DBNAME', REPAIR_ALLOW_DATA_LOSS)

7. Execute DBCC Check to Check the database error if any

DBCC CHECKDB ('DBNAME')

8. Go to Database Properties and Uncheck the DBO User Only Restricted Access option or Execute below mention query to change as Multi User Mode.

ALTER DATABASE DBNAME SET MULTI_USER

Once Steps 2 to 6 Executed Successfully without any error the suspected databases will be recovered. 

Courtesy : potools