Search This Blog

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>


 

20 comments:

Alex said...

Today I opened my SQL Server and saw that it is working badly.But I remembered about software which can help in like situations-sql server data recovery.And utility helped me quite easy and free of charge.Moreover application showed me how it can help with this problem and retrieve the data, that was considered to be lost.

james mascarenhas said...

The article is well articulate but those who don't have that sort of technical understanding can take a shorter way out to repair their database. We have designed a software which will bring back your database to a completely normal state. SO check out our SQL Database Recovery tool and see your corrupt data transforming into a healthy file.

Mercy Lerry said...

Nice presentation, the method is easy to understand, but if the database is crucial and sensitive and if the user is not comfortable with the coding part they should try out a much simpler way and get their data back. For recovery of database safe and easily the user should install dedicated SQL database recovery software and get access to their data back.

john bell said...

Sql database recovery is very necessary if the MDF file is deleted or corrupted. This is the important file of sql database. I have recovered the sql database via sql database recovery tool.

Robert Keys said...

If you are facing corruption issues in your SQL Server database then you can use SQL recovery software which is especially designed for corrupted or damaged MDF file that can help you to easily repair and recover SQL database such as tables, triggers, functions, stored procedures and views etc. It also provides free demo facility, so you can download this tool from here: http://www.filesrepairtool.com/sql-database-repair.html

Kaye R. Jenkins said...

This tool is equipped with powerful QFSCI algorithms which helps in easy recovery of every bit of SQL database. The software repair tables, views, triggers, stored procedures, damaged unique keys, primary keys and foreign keys efficiently.

Read More: http://www.filesrecoverytool.com/sql-database-repair.html

James Broderick said...

The SQL database may get corrupt due to several reasons that is, virus attacks, abnormal system shut down, network issues etc. This tool efficiently recovers entire MDF files including triggers, tables, keys, procedures, indexes. It automatically rebuilds a new database MDF file making it the best tool among many others in the market - SQL Server Fix Toolbox

Try:- http://www.sqlserver.fixtoolbox.com/

Stanley D. Middleton said...

SQL database recovery software repairs corrupt MDF file of the SQL Server database created all the SQL Server including 2012/2000/2005/2008/2008 R2 and gracefully recovers SQL XML data type files also.This SQL recovery tool recovers MDF file, NDF File components like triggers, tables, views, rules and stored procedure with ease as well as recovers the deleted tables of the SQL database..

Read More:- http://www.recoverydeletedfiles.com/sql-database-recovery-software.html

Jackson Hewitt said...

I just want to suggest SQL database recovery tool. It is a reliable solution to recover corrupt MDF file and repair as well from corrupt and damaged database. Download free SQL file repair tool with new and latest offers: http://www.mdfrecovery.org

Jim Craigan said...

Corruption in SQL server database can happen due to any uncertain like virus attack, power failure, hardware issue, OS malfunction, sudden system shutdown, so on. When a SQL server data file (.mdf) is corrupt then DBAs can try several methods to repair and recover data from it.

Run DBCC CHECKDB: It checks & reports all the error message in errorlog, if there is any problem with the database. Try to analyze & understand the error message logged in the errorlog. Re-run DBCC CHECKDB with the recommended minimum repair option to repair the file.

Detailed information about DBCC CHECKDB is available here: http://www.techrepublic.com/forums/discussions/sql-database-recovery-from-corrupt-database-file/

Restore from backup: If above command fails to fix the error message the try to restore the database from clean backup.
3rd party tool: Try 3rd party Recovery Toolbox for SQL Server to recover data from corrupt mdf file. Most of the software have demo version that shows the preview of corrupt SQL server data file.

http://www.sql.recoverytoolbox.com/

Kaye R. Jenkins said...

SQL database recovery software is provide an easy solution for damaged SQL database. This software is a perfect SQL database recovery software that helps to repair MDF and NDF files from SQL database.

You can try this tool from here:- http://www.pcrecoveryutility.com/sql-database-recovery.html

John Brad said...

This is excellent post. It’s having good description regarding this topic. It is great help for everyone. I have known much information from this. Keep up the great job. Read More information visit here :: Repair MDF File of SQL Server


Thanks
Regards.

john phlip said...

I would like to refer an excellent or result-oriented software, Kernel for SQL Database Recovery Tool.This software recover all tables, stored procedure, functions, views, rules, triggers and associated Primary Key, Unique keys, data types & all other components. To Know more detail click here - http://www.sqlrepair.org

Damian Higgins said...

SQL Server Recovery Software are basically designed to control the transaction log maintenance and to help you recover your data from a disaster. There are basically three different types of recovery models available in SQL Server 2000 and higher versions namely Simple, Full and Bulk Logged. The choice of a specific recovery model purely depends up on the criticality of the data which will be stored within the database.

For Download :- http://www.undeletepcfiles.com/sql-database-recovery-tool.html/

Richard Wright said...

I suggest you to use SQL database recovery software that is specially designed for such purposes. You can try their demo version and they apparently show you the recovered database, and if you do see your deleted tables then you can safely save them too.

Download:- http://www.mannatsoftware.com/stellar-phoenix-sql-server-recovery.html

Maria Clark said...

Download SQL Database Recovery application and retrieve MDF and NDF file from SQL server database. This application is compatible with all SQL version such as 2012, 2008R2, 2008, 2005 & 2000. Get more detail: http://www.softmagnat.com/sql-database-recovery.html




Jason Clark said...

Try SQL MDF Recovery Software to recover your corrupted MDF and NDF file database.

Chris Martin said...

If you are too getting same error as i have same issue then you must try MS SQL Database Repair Software.

Frank Norris said...

To find the authentic SQL Server Recovery software that facilitates the recovery of corrupted or damaged .MDF files from the latest SQL Server versions and recover data from corrupt and damaged SQL database files, you need to check out the SQL Server Recovery software’s features and functionalities by downloading it. - See more at: https://softcart.wordpress.com/sql-database-recovery-software/

John Brooks said...

To fix all error of SQL server data use SQL Database Recovery software is the best SQL database recovery tool that comes with plethora of options to recover data from damaged or corrupt SQL databases. The software can repair both the MDF and NDF SQL database files.

So download now: http://www.tools4recovery.com/sql-database-recovery.html