The .mdf and .ldf file extensions are typically associated with Microsoft SQL Server database files.

The .mdf file extension refers to the primary data file that stores the actual data for a SQL Server database. This file contains the tables, indexes, and other objects that make up the database schema. It also stores the actual data that is inserted, updated, and deleted by the database users.

The .ldf file extension refers to the transaction log file that stores the changes made to the database since the last backup. This file is used to roll back transactions and to recover the database in case of a system failure or other disaster.

Together, the .mdf and .ldf files make up the SQL Server database. It is important to regularly back up these files to ensure that you can recover your data in case of a failure.

Introduction of .mdf and .ldf files

The Microsoft SQL Server database is the relational database that is used to store, retrieve the information from the database through SQL statements (Insert, Update, Delete, etc.).To maintain the information in the database, SQL Server uses database objects such as Table, View, Index, and so on. To carry information stored in database objects, SQL Server uses two different main file formats, namely .mdf and .ldf.

The .mdf file, which is the main database file for the SQL Server database, stores the database information in physical order. To store the physical file database entries (.mdf) in a logical way, SQL Server uses the transaction log file (.ldf).

What is .ldf File

The .ldf file is a transaction log file that is used in the SQL Server database to store transactional information for each entry made in the main database file (.mdf). As the .ldf file contains very critical information about a database, it can also be used to recover the database in the event of any system failure or it can be helpful in keeping the database healthy. The directory structure of the database files for each version of SQL Server depends on your instance.

What is LDF File Extension and How to view it?

LDF stands for Log Database File, which is a log file or database, compatible with MS SQL server. Efficiently keep all events / transaction logs running against the database. The main goal of using LDF files is to avoid data loss in SQL Server. Users can easily identify the file extension as .ldf. A log file is saved with the same file name as its associated MDF file.An LDF file accessed with the help of the SQL Command Line application. There are many free tools that can help you open LDF files.

.ldf File location In SQL Server

SQL Server uses the same drive for .mdf and .ldf files only because both files must make changes at the same time, which helps reduce the amount of writing time for both files.
In order to view the default location of .ldf file in different SQL Server database, one can go through below mentioned path:

• SQL Server 2005: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

• SQL Server 2008: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\
• SQL Server 2008 R2: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\
• SQL Server 2012: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\
• SQL Server 2014: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\
• SQL Server 2016: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\

So, going through the above-mentioned path one can find the location of data and log files on a particular SQL Server database.

How to View .ldf file in SQL Server ?

Except go through the root directory, to see the location of the .ldf file in SQL Server, there are some more useful commands and the database configuration page to see the default location of a database file. So to know where the SQL Server database files are located.

• Command to View the SQL Server Log File Location: SELECT name, physical_name AScurrent_file_locationFROM sys.master_files

Finding the location of a particular database log file

Suppose we have a database named My Database and we need to find the location of its log file without going through the directory. Therefore, the following script can be followed to see the exact path of a particular database log file.

• SELECT filename
• FROM sys.sysaltfiles
• where name like ‘My Database_log%’

How to Restore .MDF and .LDF Files Database in SQL Server

Many times as a programmer we have to work with database backup and restore processes. Today we will learn how to attach your .MDF .LDF files and create a database .bak file in SQL Server.I have EMP.mdf and EMP.ldf files on my drive and now I want to attach these files and I want to create my EMP database.

• You can place them in the appropriate folder containing the other SQL databases on the server. In General setting this folder will be c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA
  but it depends on your SQL Server Setup time; data directory path may be in a different location.
• Open your Microsoft SQL Server and right click on Database and Click on Attach
• It will open the below dialog, for adding a file click on ‘Add’ Button
• Locate your .MDF file from your Drive, here in my case D:\Ankur\db backup\Data\EMP.mdf. Select the file and click on Ok button.
• Remember the Default Path is c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA. But I am going to place the .mdf file in a different path and try to attach the file.
• It will display Data file (EMP.mdf) and Log file (EMP.ldf)
• it will create an EMP database with data, you can check your MS SQL Object Explorer -> database and it will show the database attached, here it is EMP database.

Also Read: How to Lock or Unlock SD/Memory Card