How to Recover from a Corrupted Database in SQL Server 2000

Make sure that the database being restored is from the same Product level as the server you are restoring to. I once faced an issue when trying to restore a database from an SP4 to a non-SP4 database server.

1. Detach the corrupted MDF from Enterprise Manager.

2. Save the corrupted MDF to a safe location.

3. Now create a new database with the same name (the location must be the same as the “corrupted MDF and LDF”.

4. Stop the SQL Server service.

5. Now replace this MDF and LDF (new database) with the “corrupted MDF and LDF”.

6. Start SQL Server.

7. Launch Enterprise Manager. Now you should be able to see the database in suspect mode.

8. Launch Query Analyzer and select the master database. Issue the following commands in this order:
sp_configure ‘allow updates’, 1
go

reconfigure with override
go

9. You can now make changes to the system catalog tables.

10. Now to check the status of our database. Issue the following command

select status from sysdatabases where name=’your database name’ (replace with your database name)

11. Now execute the following command:
update sysdatabases
set status = 32768 where name=’your database name’

12. Next restart SQL Server Service.

13. You will now be in Emergency Mode.

14. Now create a destination recovery database, called dbrecover and try to push data from the corrupt database to the new dbrecover database using DTS.

15. Now issue the following undocumented command:
DBCC rebuild_log (‘your database name’, ‘new log filename with path’)

16. After this successful execution, you will need to get into your database, so issue the following commands:
use master
go

sp_dboption ‘your database name’,‘single_user’,’true’
go

DBCC checkdb(‘your database name’,repair_allow_data_loss)
go

17. Finally set your database back to a normal status by issuing the following command:
use master
go

update sysdatabases
set status=0 where name=’your database name’
go

18. Now you can see that your database is back online!

19. As a precautionary measure, please disable updates the system catalogs immediately by issuing the following command:
use master
go

sp_configure ‘allow updates’,0
go

Reconfigure with override
go

This is how one recovers a corrupted mdf database and brings it back online.

If you found this article useful, please visit our sponsors who keep us going...