Suspect Query SQL 2000 – Recover Suspect SQL 2000 Databases

I got this simple solution when I was troubled with restoring a suspect ‘ Counter’ db and found it working fine for recovering SQL 2000 databases.

All Steps are given below:

1.Right click on suspected database and select option - Take Offline

2.Stop SQL Server 2000 Service Manager and copy .mdf and .ldf files of suspect database to a safe location.

3.Rename log file ( .ldf file ) of suspect database.

4.Copy .ldf file path (with file name before renaming) and paste in below query ( in no.7 )

5.Start SQL Server 2000 Service Manager - Open Query analyzer and execute the query. 

Use master Go sp_configure 'allow updates', 1

Reconfigure with override

Go

6. From Query Analyzer execute script

Update sysdatabases set status= 32768 where name = 'Counter '

7. Restart MSSQLSERVER service, the database will be in Emergency mode

8. Rebuild Log. From QA execute script


DBCC REBUILD_LOG ('Counter', 'D:\Databases\Counter_log.ldf')

You got a Message - Warning: The log for database 'postman' has been rebuilt.

9. From QA execute script

Use master

Go

sp_configure 'allow updates', 0

Enter suspect database name and path of log file within single quote.

6.Right click on suspect data base and select refresh option and see it changes to dbo use only.

7.Right click on dbo use only db and select Properties - select Options tab - Access – and remove tick mark on Restrict access and click OK.

Note: If data base changed to " suspect/emergency " mode and unable to do anything further due to double click or selecting other options , 

Then do the following steps: 

1.Delete that suspect database , delete .mdf and .ldf files of that db and create a new database in the same name of that Suspect DB or restore with any old backup file of that DB.

2. Copy and paste .mdf and .ldf files of Suspect database to the original database file location (eg:- 'D:\Databases\ ' from where the files are copied in Step 2 

3. See the database again changed to suspect mode only

4.Repeat steps 3 to 7. 

Thanks to Sri. Aswaj Sivadas, System Administrator, Calicut Civil HO sa2cltcivilho@gmail.com

Shared by: Roy.K.J, Sulthan Bathery MDG -673592. sa2kalpettaho@gmail.com

Source :- sapost.blogspot.in