What Are MDF And NDF Files And How To Recover Them ?

SQL is a Relational Database Management System introduced to users for creating and maintaining large scale databases without inconvenience. It is programmed to create database primarily in three different types:

What Is Primary Data File (MDF)

A Primary data file is the beginning of an entire database and points towards other data files that it consists of. There is one primary data for every database. The most recommended file extension for these primary database files is “.mdf” and they consist of database objects like; triggers, tables, views, stored procedures, etc.

What Is Secondary Data File (NDF)

Meanwhile, Secondary data files consist of all data files except for primary data files i.e. there could be only one primary data while it isn’t necessary that there be a secondary data file either there would be multiple files or none. The specialty of secondary data file is that; although it consists of the data files of a database which consists of primary data files, secondary data file can be stored separately on a different drive than the one having its primary data. Most commonly recommended extension for these files is “.ndf”.

What Is Log Data File (LDF)

However, coming over to LOG data files; they consist of the entire log information which is can later be used for recovering database. Minimum one log file for every database is necessary while multiple log files can also be owned for the same. The logging level set up for database determines size of a log data file. Recommended file extension for log data files is, “.ldf”.

Accumulation

Basically, the chief data file of an SQL Server database is .mdf which when outgrows supports creation of a secondary data file to be attached to it for storing data from then on. And a log data file logs all these information for future purpose i.e. recovering database.

How To Recover MDF And NDF Files?

Database data files could end up damaged or corrupted due to outgrowing its default storage size, coming under suspect mode, modification of database while performing DROP statement, torn page, etc.

There are multiple ways of recovering a database out of such severe damage causing corruption issues which has been categorized as;

1. Restoration From Backup – You can use a last good known backup of your database and restore database contents backed up on it.
2. Run DBCC CHECKDB Repair Mode –Check the integrity of your damaged database and with repair mode rectify the damage caused.
3. Rebuild The Database – Using command lines you can perform a rebuild of the affected database and restore it back in its form.
4. Avail A Commercial Solution – When none of the above mentioned utilities or methods work go for the purchase of a commercial tool.

Try Piecemeal Restores Instead

NOTE: The topic only applies for the Enterprise Edition of SQL Server 2005 version and later adaptations and meant for restoration from database backup.

Basically the Piecemeal Restores was introduced by SQL Server 2005 edition for databases consisting of more than one file or even filegroups. It was only proposed for files or filegroups falling under simple recovery model and read-only category for restoring plus recovering them via multiple stages.

 How It Works – It engages multiple series of database restore sequences beginning from primary filegroup or primary data file and also in many cases; secondary datafiles or filegroups in individual or multiple numbers. It carries out a check for making sure that database remains consistent till completion.

Once sequence of restore is finished, if there are any recovered valid data files consistent with database; they can be brought directly online on the Server.

Making use of the piecemeal restore procedure you can recover not only MDF but also NDF associated with the primary data file.

Conclusion: However, rather than going for a long and complicated way of recovery, it is suggested to prefer a third party solution instead. You can go for SysTools SQL Recovery software available at a reasonable price along with a freeware trial version for testing.