SQL Server Database Files

Introduction

In this article, I describe SQL Server database files. A SQL Server Database is a collection of data objects containing two operating system files. One is a data file, and another is a log file. Data files consist of data and objects such as tables, indexes, Stored Procedures, and views. Log files contain the information required to recover all transactions in the database. Data files can be grouped in file groups for allocation and administration purposes.

SQL Server allows the following three types of database files.

Primary data files

Every database has one primary data file that contains a track of all the remaining files in the database, in addition to storing data. By agreement, the extension of a primary data file is MDF.

Secondary data files

A secondary database may be zero or more data files. According to requirements, the extension of a secondary data file is NDF.

Log files

Log files hold all the log information to recover the database. Every database must have at least one log file; the extension of a log file is LDF.

How to create database files?

When we create a database, the SQL Server Database contains two operating system files, MDF and LDF.

If you want to create additional data files, such as secondary data files like NDF, we can use the following procedure.

Right-click on the database name.

Select "Properties" > "Files."

We will see the two operating system files, MDF and LDF, as in the following.

Now click the "Add" button.

Now we can define file names like in the following screen.

Then click the "OK" button.

New data files have been created; we can check using the following query.

SELECT name,
       RIGHT(physical_name, 4) AS File_DataType,
       type_desc               AS [DESCRIPTION],
       physical_name
FROM   sys.master_files
WHERE  name LIKE 'testing%'

select * from sysfiles