MySQL Connector/Python

Recover data from .mdf file

Asked by Montgomery

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.

Question information

Language:
English Edit question
Status:
Invalid
For:
MySQL Connector/Python Edit question
Assignee:
No assignee Edit question
Solved by:
Geert JM Vanderkelen
Solved:
Last query:
Last reply:
Revision history for this message
kamil.horacek (kamil-horacek) said : 		#1

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.
Also, I think that this information will be useful to you.
https://technet.microsoft.com/en-us/library/ms174385.aspx
http://community.office365.com/en-us/f/172/t/266070.aspx

Revision history for this message
Montgomery (emerysmontgomery) said : 		#2

Thanks for your response, 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.

Revision history for this message
kamil.horacek (kamil-horacek) said : 		#3

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.
If that doesn't work then there is an option to try to restore database using SQL Server Recovery Toolbox. It is a powerful tool for data recovery. They have a demo version. So you can check it works in your situation or not. http://www.oemailrecovery.com/sql_repair.html

Revision history for this message
Montgomery (emerysmontgomery) said : 		#4

Thanks kamil.horacek, that solved my question.

Revision history for this message
Montgomery (emerysmontgomery) said : 		#5

You've been very kind in your efforts to help. Thanks for that. Solved!

Revision history for this message
crickwilli (crickwilli) said : 		#6

Get an effective result-oriented MDF file recovery software, download third-party tool Kernel for SQL Database Recovery Tool. The tool quickly recovers all tables, stored procedure, functions, views, rules, triggers and associated Primary Key, Unique keys, data types & all other components. It supports all versions of MS SQL server and Windows OS versions. You can also download free demo version - http://www.mdfrepairtool.sqldatabaserecovery.org

Revision history for this message
Matthewrobbin (matthewrobbin) said : 		#7

Easy to recover complete database objects available in .MDF files such as tables, views, stored procedures, triggers, indexes, primary keys, unique keys, foreign keys and etc. See at: http://www.recoverydeletedfiles.com/sql-database-recovery-software.html

Revision history for this message
Geert JM Vanderkelen (geertjmvdk) said : 		#8

This question has nothing to o with Connector/Python.

Revision history for this message
Anja Baader (anjabaader) said : 		#9

To repair corrupt or damaged MDF files, I suggest an effective software Stellar Phoenix for SQL Database Recovery Tool. The tool recovers all tables, stored procedure, functions, views, rules, triggers and associated Primary Key, Unique keys, data types and etc. It supports all versions of MS SQL server and all updated Windows OS versions. Reference link - http://www.softmagnat.com/sql-database-recovery.html