Sql Server Database Files And Filegroups Complete Guide

 Last Update:2025-06-23T00:00:00     .NET School AI Teacher - SELECT ANY TEXT TO EXPLANATION.    7 mins read      Difficulty-Level: beginner

Understanding the Core Concepts of SQL Server Database Files and Filegroups

SQL Server Database Files and Filegroups (Explained in Detail with Important Information)

Overview of SQL Server Database Files

  1. Primary Data File (.mdf)

    • This is the first or primary file in a database. It contains metadata about the database and points to the other files in the database.
    • Every database has one primary data file.

  2. Secondary Data Files (.ndf)

    • Secondary data files are optional and can be used to split up large databases or separate data objects for maintenance purposes.
    • Secondary files can improve performance by distributing the database load across multiple filegroups.

  3. Log File (.ldf)

    • The log file contains all the information necessary for recovery on SQL Server databases.
    • It allows for point-in-time recovery and rollback of transactions.

  4. Full-Text Catalog (ftdata)

    • Used for storing full-text indexes, which enable text-based searches.
    • It improves text retrieval performance in applications.

File Groups in SQL Server

  1. Primary Filegroup

    • Contains the primary data file.
    • All database objects, like tables and indexes, are created in the primary filegroup unless specified otherwise.

  2. Secondary Filegroups

    • These are optional and can contain one or more secondary data files.
    • Secondary filegroups can be used for specific purposes such as separating read-only data, mirroring subsets of data, or improving performance through supported data placement.

  3. Filegroup Types

    • Default Filegroup: Data objects are created in the default filegroup unless explicitly stated otherwise.
    • Read-Only Filegroup: Used to store data that should not be modified.
    • Memory-Optimized Data Filegroup: Specifically designed to store memory-optimized tables.

Important Information

1. How to Create Database Files and Filegroups:

CREATE DATABASE MyDatabase
ON PRIMARY (
    NAME='MyDatabase_Data',
    FILENAME='C:\SQLData\MyDatabase_Data.mdf',
    SIZE=10MB,
    MAXSIZE=100MB,
    FILEGROWTH=5MB
),
FILEGROUP SecondaryFG1 (
    NAME='SecondaryFG1_Data',
    FILENAME='C:\SQLData\SecondaryFG1_Data.ndf',
    SIZE=5MB,
    MAXSIZE=50MB,
    FILEGROWTH=5MB
),
FILEGROUP SecondaryFG2 (
    NAME='SecondaryFG2_Data',
    FILENAME='C:\SQLData\SecondaryFG2_Data.ndf',
    SIZE=5MB,
    MAXSIZE=50MB,
    FILEGROWTH=5MB
)
LOG ON (
    NAME='MyDatabase_Log',
    FILENAME='C:\SQLData\MyDatabase_Log.ldf',
    SIZE=5MB,
    MAXSIZE=25MB,
    FILEGROWTH=5MB
);

2. Moving Database Files or Filegroups:

Database files and filegroups can be moved to a different location after they are created using the ALTER DATABASE statement.

ALTER DATABASE MyDatabase
MODIFY FILE (
    NAME='SecondaryFG1_Data',
    FILENAME='D:\SQLData\SecondaryFG1_Data.ndf'
);
GO

3. File and Filegroup Maintenance:

Regular maintenance activities such as file growth management, shrinkage, and reconfiguration are essential.

  • Growth: Managed automatically or manually using the ALTER DATABASE statement.
  • Shrinkage: Can be performed using the DBCC SHRINKFILE command.

4. File-Based Backup and Restore:

SQL Server allows for file-based backup and restoration, which can be useful for managing large databases.

BACKUP DATABASE MyDatabase
TO DISK='C:\Backups\MyDatabase_Full.bak'
WITH FORMAT, MEDIANAME='MyBackups', NAME='Full Database Backup';
GO

BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log.bak'
WITH FORMAT, medIANAME='MyBackups', NAME='Daily Log Backup';
GO

5. Key Considerations:

  • Performance: Placement of files and filegroups on multiple drives or disks can improve read/write operations.
  • Recovery: Careful planning is required to ensure that the log file size and backup strategies meet recovery time objectives (RTOs) and recovery point objectives (RPOs).
  • Scalability: Properly configured filegroups can facilitate the scaling of databases as the amount of data grows.

Online Code run

🔔 Note: Select your programming language to check or run code at

💻 Run Code Compiler

Step-by-Step Guide: How to Implement SQL Server Database Files and Filegroups

Introduction to SQL Server Database Files and Filegroups

What are Database Files?

  • Primary (MDF): This is the main data file. Every database must have at least one primary data file.
  • Secondary (NDF): These are optional additional data files that can store the same type of data as the primary data file.
  • Log (LDF): Every database has one or more log files which hold the transaction log information necessary for recovery.

What are Filegroups?

Filegroups allow you to logically group data files together. This is useful for:

  • Data placement and performance tuning.
  • Moving data files to different disks or arrays.
  • Backup and restore operations at the filegroup level.

Step-by-Step Examples

Step 1: Create a Simple Database with One Filegroup

Let's start by creating a simple database with one filegroup, which is the default behavior.

USE master;
GO

CREATE DATABASE SimpleDB
ON PRIMARY (
    NAME = 'SimpleDB_Data',
    FILENAME = 'C:\SQLData\SimpleDB_Data.mdf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB
)
LOG ON (
    NAME = 'SimpleDB_Log',
    FILENAME = 'C:\SQLData\SimpleDB_Log.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB
);
GO

Step 2: Verify Database and Its Files

Let's verify that the database and its files were created correctly.

USE master;
GO

EXEC sp_helpdb 'SimpleDB';
GO

SELECT 
    name AS Logical_Name,
    physical_name AS Physical_Name,
    type_desc AS File_Type,
    size/128.0 AS File_Size_MB,
    max_size/128.0 AS Max_Size_MB,
    growth AS File_Growth
FROM sys.master_files
WHERE database_id = DB_ID('SimpleDB');
GO

Step 3: Add a New File to the Primary Filegroup

Suppose we want to add another data file to the primary filegroup.

USE master;
GO

ALTER DATABASE SimpleDB
ADD FILE (
    NAME = 'SimpleDB_Secondary_Data',
    FILENAME = 'C:\SQLData\SimpleDB_Secondary_Data.ndf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB
) TO FILEGROUP [PRIMARY];
GO

Step 4: Verify the New File

Let's check if the new file was added correctly.

SELECT 
    name AS Logical_Name,
    physical_name AS Physical_Name,
    type_desc AS File_Type,
    size/128.0 AS File_Size_MB,
    max_size/128.0 AS Max_Size_MB,
    growth AS File_Growth
FROM sys.master_files
WHERE database_id = DB_ID('SimpleDB');
GO

Step 5: Create a New Filegroup and Add a New File to It

Let's create a new filegroup and add a new data file to it.

USE master;
GO

ALTER DATABASE SimpleDB
ADD FILEGROUP NewFilegroup;

ALTER DATABASE SimpleDB
ADD FILE (
    NAME = 'SimpleDB_NewFilegroup_Data',
    FILENAME = 'C:\SQLData\SimpleDB_NewFilegroup_Data.ndf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB
) TO FILEGROUP [NewFilegroup];
GO

Step 6: Verify the New Filegroup and File

Let's verify that the new filegroup and file were created correctly.

EXEC sp_helpfilegroup @groupname = 'NewFilegroup';
GO

SELECT 
    name AS Logical_Name,
    physical_name AS Physical_Name,
    type_desc AS File_Type,
    size/128.0 AS File_Size_MB,
    max_size/128.0 AS Max_Size_MB,
    growth AS File_Growth
FROM sys.master_files
WHERE database_id = DB_ID('SimpleDB');
GO

Step 7: Create a Table in the New Filegroup

Let's create a table and specify that it should be placed in the new filegroup.

USE SimpleDB;
GO

CREATE TABLE Sales (
    SaleID INT IDENTITY(1,1) PRIMARY KEY,
    SaleDate DATE NOT NULL,
    Amount DECIMAL(18,2) NOT NULL
) ON NewFilegroup;
GO

Step 8: Verify Table Placement

Let's verify that the table was created in the correct filegroup.

You May Like This Related .NET Topic

📥 Download Code ▶️ Watch Online 📖 Read Description

Login to post a comment.