Recover data from .mdf file

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 curcial for me.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Well, .ndf files are per default secondary data files. Without a backup of it, you will definitly have data loss. The severity depends on your file/file group to table distribution.

When you have the Enterprise edition, than you can do a partial restore of the primary file group. Otherwise it's not possible to restore the database without the .ndf files.

When you know the exact configuration of your database, tables files and filegroups, then you can create a dummy .ndf and use this file in a restore operation.

The easiast solution: restore it from your backup.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ankurdkAuthor Commented:
Thanks Ste5an for your reposnse, I have restored the database from a backup and I've got the .ndf file. But there are two issues :

1) the new file created after restoring carries a different .ndf file name.
2) How do i link the latest .mdf & .ldf files with it. Is it at all possible.

Thanks in advance.
ste5anSenior DeveloperCommented:
Detach you database. Overwrite the mdf and ldf files you've restored from your backup with your files from the D: drive. Reattach the database.

There is no guarantee that this will work or if it works, that your database is in a state where you can continue to work with.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vitor MontalvãoMSSQL Senior EngineerCommented:
I have restored the database from a backup and I've got the .ndf file
To where you did the restore?

1) the new file created after restoring carries a different .ndf file name.
Can mean that someone changed the filename since the last backup. Or maybe there are more .ndf files and you're working with the wrong one.

2) How do i link the latest .mdf & .ldf files with it. Is it at all possible.
How old is the backup?
ankurdkAuthor Commented:
Restored from last backup. Thanks all for the support.
ankurdkAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for ankurdk's comment #a40693221

for the following reason:

Could not find the exact solution.
ste5anSenior DeveloperCommented:
There is no "exact" solution for your case. Simply because when you loose one data/log file, you need to restore this either by hand or from backup. So data-loss is highly predictable.

The problem is your situation: Here you normally always restore entirly from backup, which is the correct approach.

btw, this was also my recommendation in my first answer.

So points should be given.
Vitor MontalvãoMSSQL Senior EngineerCommented:
ankurdk, please review the answers and chose which ones helped you to find a solution, even isn't the solution that you might expected at the beginning.
ankurdkAuthor Commented:
Well, I respect the objection of Vitor and claim to points by Ste5an. I would like to humbly state that restoring from a backup was my last resort which I was fully aware from the very beginning so I need not even mention it. I did mention the approaches that I took to recover data from existing non-functional database to which I expected answers which I got and I appreciate it. Nevertheless, I would like to credit Ste5an for his constant suggestions. Once again, thanks all for your support.
ankurdkAuthor Commented:
Thanks a lot.
Fabia ThompsonCommented:
The recovery tool I'd like to recommend to you is sd card recovery software  which is able to recover lost photos/videos easily with simple clicks.Download and install H-Data Recovery Master(
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.