Ads 720 x 90

How to repair MDF files not detached from SQL Server


If you have an mdf file that was not properly detached from SQL Server (possibly due to a hard drive crash), then you may need to repair the mdf before you are able to attach the database. 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 .)
















  13. 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=´datafilename´, NEWNAME = ´eshadata´)
    go
    alter database eshadata
    MODIFY FILE(NAME=´logfilename´, NEWNAME = ´eshadata_Log´)
    go
    dbcc checkdb(´eshadata´)
    go
    sp_dboption ´eshadata´,´dbo use only´,´false´
    go
    use eshadata
    go
    sp_updatestats
    go

  14. 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:\dvd.mdf´,´h:\DVD.ldf´

 


<a href="http://www.esha.com/support/kb/sql/repairmdf">http://www.esha.com/support/kb/sql/repairmdf</a>


 

Related Posts

Total Pageviews

Subscribe Our Newsletter