Recover data from .mdf file

By Montgomery
Jan 19, 2016
Post New Reply
  1. Dear All,
    Hi. I have a server with the following installed:
    Windows server 2008 R2 SP1
    SQL Server 2008 R2
    My production database was "mydb"
    The production database files were stored as below :
    1) "mydb.ndf" file was stored at "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mydb.ndf"
    2) "mydb.mdf" and "mydb.ldf" files were stored at D:\myfolder
    Due to some reasons the Hard disk crashed and I lost my C:\ drive leaving me with only the ".mdf" and ".ldf" database files.
    I have re-installed the Windows server 2008 R2 SP1 and SQL Sever 2008 R2. Now when I try to "re-attach" "mydb", by following method :
    Right click Databases->Attach->Add, select the .mdf file and click OK
    I get the following error :
    "Unable to open the physical file C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mydb.ndf". Operating system error 2: "2(The system cannot find the file specified.)" (Microsoft SQL Server, Error : 5120)
    My Query :
    How can I recover/restore the database from just .mdf and .ldf files ?
    I've tried the following :
    1) Creating a "mydb.ndf" file and saving at the same location but SQL server is not able to map it to "mydb.mdf".
    2) Creating a new "mydb" and over-writing "mydb.mdf" and "mydb.ldf" to the newly created location.
    Please advice a solution as this recovery is very crucial for me.
  2. jobeard

    jobeard TS Ambassador Posts: 11,158   +986

    I am afraid that the mdf & ldf are control files and the real data was in the ndf
    You need a database dump or backup to proceed.
  3. verneronomous

    verneronomous TS Enthusiast Posts: 43

    Man horrible however there was one time I had some luck in a similar situation. I ended up taking the ldf and or MDF files and attaching them to another instance and was able to recover the data. Your master should still contain all the user info and should have kept all the jobs you may have set up. If you couldn't recover that way you could try to add them manually:

    ssms -> rightclick database -> attach ; choose the files and attach

    possibly you could run a 'DBCC CHECKDB()' statement against the newly attached database then you can copy the .mdf and .ldf files to a different server and just Attach them. Here is the supporting Microsoft Article:

    If that didn't work then it the DB could be corrupt and then I dont know.

    I found this supporting article also. Hopefully you can get the DB back its always tricky:

    Good luck man

Similar Topics

Add your comment to this article

You need to be a member to leave a comment. Join thousands of tech enthusiasts and participate.
TechSpot Account You may also...