-- Recover a corrupt database
Author Nigel Rivett
The first step is to attach the database as suspect
Create a database with an mdf of the same size as the old one
stop the server.
Copy the corrupt mdf over the newly created one.
Restart the server - the database should be suspect.
Get the server to retry recovery
Reset the suspect flag
this can be done by executing sp_resetstatus 'mydbname'
update master..sysdatabases SET status = status ^ 256 where name = 'mydbname'
Restart the server
If the database is still suspect set it to emergency mode
This will be the case for torn pages - if you get a torn page error go straight to this step.
update master..sysdatabases set status = 32768 where name = 'mydbname'
(for v7 I believe it was update master..sysdatabases set status = -32768 where name = 'mydbname')
The database should now be in emergency mode and allow you to access the data.
The data should now be transferred to another database via dts, bcp or queries (I prefer bcp native format).
You will get an error when trying to access the corrupt data but should be able to access all the rest of the data.
Use indexes to access data around the corrupt pages.
To allow the above commands to work you will have to allow updates to system tables
Sp_configure 'allow updates', 1
Reconfigure with override
Remember to set it back afterwards