SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
How to Recover from a Corrupted Database in SQL Server 2000
Anil Mahadev About the Author

Anil was the world's first featured contributor for the IBM DB2 Express-C product on its web site and is currently serving on the Board of Leaders for the DB2 India User Group. He was a major player in organizing IDUG India 2009 and works full time as a SQL Server and SharePoint Consultant and is loving every day of it.

India TechNet also crowned him as Microsoft's first ever Efficiency Super Hero! To get in touch with Anil or read more of his technical articles, please visit his web site.

Typepad Profile Anil Mahadev on Twitter
Send to a Friend Printer Friendly Version PDF
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...

SQL Defrag Studio 2009
Fragmented indexes can cause your SQL Server to gradually slow down until it grinds
to a complete stop. Performance may be slow but you'll need to solve it fast.

SQL Defrag Studio finds fragmented indexes and automatically defrags them for you.
Download
Send to a Friend Printer Friendly Version PDF
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form