In an era of computing, MDF can be expanded as “Master Database file”. Database is a collection of tables that is used for data storage. These files are extremely important as without Master database you cannot run the SQL Server. This file is used to store system level information for running server configuration and records the presence of all the existing database along with their location and records any initialization for the server.
Microsoft SQL Server supports different kinds of data types such as Integer, Char, Float, Decimal, Varchar, Binary, Text etc. The primary data present in database are stored in.mdf and .ndf file allows the data in a database to be used across the other files whereas .ldf is generally used to store log files.
Every database contains one primary database file and one or more secondary database file (user-defined).
Primary Data File consists of the initial information and refers to information in another file. The most commonly used extension for this data file is .mdf.
Secondary Data Files are optional files and are generated by users which can be created and deleted at user’s will.
You can retrieve data from SQL Server database by querying. The Structured Query Language (SQL) helps you to retrieve data in a powerful and flexible way. The SQL Server returns a query in the form of record which are stored in recordset. Recordset is a table present in servers memory which contains rows of data (record). It is further subdivided into fields (columns).
You must always create a backup of the master database because if the master files get corrupted, it can be restored by the backup created. Sometimes when the database is corrupted SQL Server cannot be started at all. These files also have a compact version in Windows Phone popularly known as SQL Server Compact (.sdf) files which are used for storing small amount of data and are also used in website. But it showcase a poor performance whereas when you use these MDF files it provides an advantage of using bigger sites and helps in storing a lot of data and needs to be installed on the server.
Why Secondary Database Files (NDF) Created
Whenever the data limit exceeds in a single file, secondary files can be generated. They are generally created to disperse data across multiple disks such that it can handle the fault tolerance and helps the database to grow. The extension file commonly used is .ndf. Since it is difficult to manage large files we can distribute files to reduce the load on IO Systems. Secondary database files help in easy access to data via multiple threads and increasing the overall IO performance. You cannot open the NDF file without attaching the corresponding MDF file to it.
Second important reason is Disaster Recovery. During disaster, when there is an urgency to access current data, recovery of entire data might take huge amount of time. Using secondary files allows you to recover the primary data and access these queries while the secondary data are being loaded from the warehouses. For example if a you need to recover the entire database but only requires access to the most recent data ,then the historical data can be recovered slowly while users can still work on the recent data.
You might come across a variety of issues while opening the MDF File, such as:
- Access Denied may occurs if you might not have the MDF File. You can also face this issue if you have received the file from someone else (like the user have copied the database instead of exporting) or the due to changes in file permission for a particular user.
- There might be chances that the databases are present in the Read-only format and the access to the database is denied.
- Errors may happen when you try to open then MDF of the higher version of SQL Server in a lower version.
In order to solve this problems there are third party software available in market which can solve these problems. One such Software is free SysTools SQL MDF Viewer which allows you to open MDF file without the SQL Server environment
EmoticonEmoticon