This article revolves around a trivial yet common issue. There might be a set of people for whom the current topic might appear to be insignificant. But I have been asked this question innumerable times, particularly from people who are frequenting using forums or have blog related to storage and highly availability, which instigated me to write this article.
Here goes this frequently asked question.
Question: Is it possible to restore database if one of the files of .mdf (primary data file) or .ndf (secondary data file) is missing?
Answer: In one word the answer is NO. All the .mdf and .ndf files are mandatory to attach or restore database successfully. Even though you have all the transactions stored in .ldf you will not be able to restore the database completely.
I hope this answer removes the cloud of doubt hovering over my targeted readers.
Reference : Pinal Dave (http://blog.sqlauthority.com)
pinaldave
Its good to know the answer, but that would be great if you explain your readers the reason behind ‘No’.
The reason for that is because .MDF contains all the data which are required.
Hi ALL,
Pinal stated in brief but he is correct. For freshers just simplify the things, Suppose if database contains 3 .mdf and 2 .ndf and 1 .ldf files. And play puzzle like this. Stop the SQL Server service and delete physically one of .mdf file. After that database comes in restoring position. Only the method you can brigs the database in active position when you add missing file. You must follow the rules of database recovery as by PINAL’s article. It require the previous or last backup which contains all .mdf, .ndf & .ldf files information/data. Also you can play more puzzles with Pinal.
Regards,
Rajiv Singh