How to repair MDF files not detached from SQL Server 2000

If you have an mdf file that was not properly detached from SQL Server 2000 (possibly due to a hard drive crash), the first (best) option is to restore the database from a valid backup.  If that is not an option, then you may need to repair the mdf before you are able to attach the database.

If you are using SQL Server 2000, the following are instructions on how to repair the mdf file. Replace the filenames with your filename!

1. Make sure you have a copy of eshadata.MDF (or gendata.mdf)
2. Create a new database called fake (default file locations)
3. Stop SQL Service
4. Delete the fake_Data.MDF and copy eshadata.MDF (or gendata.mdf) to where fake_Data.MDF used to be and rename the file to  fake_Data.MDF
5. Start SQL Service
6. Database fake will appear as suspect in EM
7. Open Query Analyser and in master database run the following :

   sp_configure 'allow updates',1
   go
   reconfigure with override
   go
   update sysdatabases set
      status=-32768 where dbid=DB_ID('fake')
   go
   sp_configure 'allow updates',0
   go
   reconfigure with override
   go

   This will put the database in emergency recovery mode
8. Stop SQL Service
9. Delete the fake_Log.LDF file
10. Restart SQL Service
11. In QA run the following (with correct path for log)

    dbcc rebuild_log('fake','h:\fake_log.ldf')
    go
    dbcc checkdb('fake') -- to check for errors
    go

12. Now we need to rename the files, run the following (make sure there are no connections to it) in Query Analyser (At this stage you can actually access the database so you could use DTS or bcp to move the data to another database .)
    

    use master
    go
    
    sp_helpdb 'fake'
    go
    
    /* Make a note of the names of the files , you will need them in the next bit of the script to replace datafilename and logfilename - it might be that they have the right names  */
    
    sp_renamedb 'fake','eshadata'
    go
    
    alter database eshadata
    MODIFY FILE(NAME='fake', NEWNAME = 'eshadata')
    go
    
    alter database eshadata
    MODIFY FILE(NAME='fake_log', NEWNAME = 'eshadata_log')
    go
    
    dbcc checkdb('eshadata')
    go
    
    sp_dboption 'eshadata','dbo use only','false'
    go
    
    use eshadata
    go
    
    sp_updatestats
    go

13. You should now have a working database. However the log file will be small so it will be worth increasing its size. Unfortunately your files will be called fake_Data.MDF and fake_Log.LDF but you can get round this by detaching the database properly and then renaming the files and reattaching it.
    Run the following in QA

    sp_detach_db eshadata
    
    --now rename the files then reattach
    
    sp_attach_db 'eshadata','h:\eshadata.mdf','h:\eshadata_log.ldf'