Database Files

Alexandr Omelchenko Glossary

Every database of SQL Server at least has a log file and data file. Log files contain all information that is necessary to restore transactions in the database. Data files contain data and objects (indexes, tables, etc.).

Types of Database Files

There are three types of database files in SQL Server:

  1. Primary data file. Every database has only one primary data file from which all other files in the database start. Locations of all data files in the database are recorded not only in the master database but also in the primary data file. Use *.mdf extension to mark a primary data file.
  2. Secondary data file. This type of data file includes all data files other than the primary data files. A database can have both, several secondary data files or none of them. Use *.ndf extension to mark a secondary data file.
  3. Log file. This file type is crucial for database restore process. All log information is stored in the log file. Each database must have at least one log file. Use *.ldf extension to mark a log data file.

These name extensions (*.mdf, *.ndf and *.ldf) in SQL Server are not necessary, but they help us to discern the types of data files.

File Names

All files have logical and physical names. Physical file name is a path to the data file on disk. Logical name is a short alias that refer to the physical file. Here’s how it looks:

ALTER DATABASE Test
MODIFY FILE
NAME = NameTest - (logical file name)
FILENAME = 'C:\NameTest.mdf' - (physical file name)

Data File Pages

The disk space allocated to data file in SQL Server is logically divided into pages. The pages are numbered sequentially starting with zero. Because every file has its own ID number, page identification requires the page number and the file ID. The first page in each file is a file header and the following few pages contain system information. Database boot page (contains database attributes) is stored in the first log file and in the primary data file.

Database Files

Database File Size

Each file grows by growth increments, as defined by user. File size increases every time it is filled with information. It is also necessary to specify the maximum file size, otherwise the file will grow until it has used all free space on the disk.

How to Backup a Database File

You can backup a single database file named ‘DataFile’ using the following Transact-SQL command:

BACKUP DATABASE Test
FILE = 'DataFile' TO DISK = 'D:\DataFile.bak';

The other way to backup a database file is to use SQL Server Management Studio (SSMS): 

  • Right click on the database where the filegroup you want to backup is located
  • Select “Tasks”, then “Back up…”
  • Select  the backup type (“Full” or “Differential”)
  • Select “Files and filegroups”
  • Choose file and click “OK” (It is possible to select one or more individual files, or select a filegroup to automatically choose all the files in that filegroup)
    Database file backup
  • Add backup destination
  • Click “OK”