Create and Manage Filegroups in SQL Server 2025

A filegroup is, exactly as it sounds, a group of files. The previous lesson already introduced the basic syntax for adding one with ALTER DATABASE; this lesson goes deeper into working with filegroups as a whole system, the different types SQL Server recognizes, placing objects deliberately onto one, moving existing objects between them, and backing up and restoring by filegroup rather than the whole database.

Grouping files together matters most once a database's files need to live across more than one physical location. Spreading a database's data across multiple disks, whether that's separate physical drives, a SAN, or multiple volumes on cloud-hosted storage, can meaningfully improve performance by letting SQL Server read and write in parallel rather than funneling every operation through a single disk. The filegroup is what makes that manageable: rather than tracking and administering each individual file separately, you administer the filegroup as one unit, and a change that needs to apply to the whole group, placing a new table on it, backing it up, marking it read-only, only has to be made once, against the filegroup, rather than once per file.

A single database comfortably living in its default primary filegroup never needs any of this; adding filegroups is a deliberate choice made for a specific reason, not a default step every database should take. The two reasons that come up most often are spreading I/O across more physical hardware for performance, and separating data by how it's actually used, active, frequently-queried data in one filegroup and older, rarely-touched data in another, which is exactly the role SalesArchive has played in this course's running example since the lesson on creating a database.

Every SQL Server database has at least these:

• Primary: the filegroup containing the primary database file (.mdf), along with every system table.
• Default: the filegroup that holds any new table, index, or other object that doesn't specify a filegroup explicitly when it's created. Only one filegroup can be the default at a time, and if none is ever designated, the primary filegroup is the default by, well, default.
• User-defined: any additional filegroup created with CREATE DATABASE or ADD FILEGROUP, beyond the primary filegroup, that isn't currently set as the default.

Changing which filegroup is the default requires membership in the db_owner role, the same permission the earlier lesson on database permissions covered:

ALTER DATABASE SalesDB
MODIFY FILEGROUP SalesArchive DEFAULT;

The GUI route to the same change runs through SQL Server Management Studio: right-click the database, choose Properties, open the Filegroups page, and mark the desired filegroup as the default there instead of running T-SQL directly. Either path ends up changing the same underlying setting.

Beyond the ordinary filegroups covered above, a database can have two special-purpose filegroup types built for specific features rather than everyday table storage:

ALTER DATABASE SalesDB
ADD FILEGROUP FS_FG CONTAINS FILESTREAM;

ALTER DATABASE SalesDB
ADD FILEGROUP MOD_FG CONTAINS MEMORY_OPTIMIZED_DATA;

A FILESTREAM filegroup stores large binary data as ordinary files on the file system rather than inside the database's own data file, which can be considerably more efficient than storing very large binary objects directly in a table. It requires FILESTREAM to be enabled at the instance level first, through SQL Server Configuration Manager or sp_configure, before it can actually be used; creating one against an instance where FILESTREAM isn't enabled fails outright.

A MEMORY_OPTIMIZED_DATA filegroup backs In-Memory OLTP tables, the kind of table SQL Server keeps primarily in memory rather than reading from disk on every access. A database can only have one of these, unlike ordinary user-defined filegroups, where there's no such limit. As of SQL Server 2025, the last memory-optimized filegroup can finally be removed entirely once every In-Memory OLTP object depending on it has been dropped first, something earlier versions didn't allow without dropping the whole database along with it, a meaningful quality-of-life improvement for anyone who experimented with In-Memory OLTP and later decided against it.

A new table or index can be placed on a specific filegroup directly, rather than landing on the default filegroup the way it would without any instruction otherwise:

CREATE TABLE dbo.ArchivedOrders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL
) ON SalesArchive;

The trailing ON SalesArchive is what does it; without it, the table would land wherever the database's current default filegroup happens to be.

Moving an index that already exists to a different filegroup works a little differently, since there's no direct "move" statement for an index. Rebuilding the index on the new filegroup, using CREATE INDEX with DROP_EXISTING = ON, does it in one step instead:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders(CustomerID)
WITH (DROP_EXISTING = ON) ON SalesArchive;

SQL Server drops the old index and rebuilds it fresh on the target filegroup in a single operation, rather than requiring a separate drop and a separate create. Moving a table itself, rather than just an index on it, is a bigger undertaking, usually involving rebuilding its clustered index onto the new filegroup, since a table's data physically lives wherever its clustered index does.

When a filegroup holds more than one file, MODIFY FILEGROUP controls how those files grow relative to each other:

ALTER DATABASE SalesDB
MODIFY FILEGROUP SalesArchive AUTOGROW_ALL_FILES;

AUTOGROW_ALL_FILES grows every file in the filegroup together whenever any one of them fills up, keeping them roughly the same size as each other over time. AUTOGROW_SINGLE_FILE, the default, only grows the specific file that actually ran out of space, which can leave files in the same filegroup at noticeably different sizes after enough growth events. SQL Server also fills multiple files within a filegroup proportionally day to day, writing more new data to whichever file currently has more free space rather than filling them in strict order, so the files tend to stay reasonably balanced even between growth events.

An archive filegroup like SalesArchive, once it genuinely holds nothing but settled, historical data that isn't going to change, is a natural candidate for the READ_ONLY setting the previous lesson introduced at the filegroup level:

ALTER DATABASE SalesDB
MODIFY FILEGROUP SalesArchive READ_ONLY;

Beyond simply preventing accidental writes to data that shouldn't change anymore, a read-only filegroup doesn't need to be backed up nearly as often, since its contents aren't changing between one backup and the next; many shops back up a read-only filegroup once, right after switching it to READ_ONLY, and then skip it in routine backup jobs afterward. Switching it back with READ_WRITE works the same way, and both directions require exclusive access to the database to actually go through, the same restriction that applies to every READ_ONLY/READ_WRITE change at the filegroup level.

Retiring a filegroup follows the same two-step pattern the previous lesson covered for removing an individual file, since a filegroup can't be removed while it still contains one: every file has to be emptied and removed first, and only then can the now-empty filegroup itself go:

DBCC SHRINKFILE (SalesArchiveData, EMPTYFILE);

ALTER DATABASE SalesDB
REMOVE FILE SalesArchiveData;

ALTER DATABASE SalesDB
REMOVE FILEGROUP SalesArchive;

The primary filegroup can never be removed this way, since it holds the database's system tables and primary data file for as long as the database itself exists; this sequence only applies to a user-defined filegroup that's genuinely no longer needed.

Two catalog views report what's actually configured on a database, the same kind of lookup the previous two lessons used for files and databases generally:

SELECT name, type_desc, is_default
FROM sys.filegroups;

SELECT name, physical_name, size * 8 / 1024 AS SizeMB
FROM sys.database_files;

sys.filegroups lists the filegroups themselves, including which one is currently marked default; sys.database_files lists the individual files and which filegroup each one belongs to. Checking these periodically, along with the actual free space on each underlying disk outside SQL Server entirely, is worth doing on a regular schedule rather than only after something's already run out of room.

For a large database, backing up and restoring one filegroup at a time, rather than the whole database in one pass, can keep at least part of the database available during the operation, and can shorten how long a single backup job takes to run:

BACKUP DATABASE SalesDB FILEGROUP = 'SalesArchive'
TO DISK = 'C:\BackupPath\SalesArchive.bak';

A filegroup backup on its own isn't automatically enough to bring a database back to a fully consistent state on restore, though. Under the FULL or BULK_LOGGED recovery models, restoring a filegroup backup generally needs to be followed by restoring the transaction log that covers the period since the backup was taken, which is what actually brings that filegroup back in sync with the rest of the database rather than leaving it at a point in time slightly behind everything else. Filegroup backup and restore is a genuinely useful tool for a large database, particularly one organized with an active filegroup and a separate, mostly-static archive filegroup like SalesArchive, but it's a feature with real prerequisites, not a simple drop-in replacement for an ordinary full database backup covered in a later course on backup and recovery.

A filegroup doesn't have to be added after the fact; it can be defined right inside CREATE DATABASE, the way the previous lesson on creating databases briefly showed:

CREATE DATABASE SalesDB
ON PRIMARY
( NAME = SalesData, FILENAME = 'C:\SQLData\SalesDB.mdf',
  SIZE = 128MB, MAXSIZE = 1024MB, FILEGROWTH = 64MB ),
FILEGROUP SalesArchive
( NAME = SalesArchiveData, FILENAME = 'C:\SQLData\SalesArchive.ndf',
  SIZE = 128MB, MAXSIZE = 1024MB, FILEGROWTH = 64MB )
LOG ON
( NAME = SalesLog, FILENAME = 'C:\SQLLogs\SalesDB_log.ldf',
  SIZE = 64MB, FILEGROWTH = 64MB );

Notice the comma separating the primary file specification from the FILEGROUP clause that follows it; both belong to the same ON clause, with LOG ON only starting once every data filegroup has been listed. Defining a filegroup up front this way, at creation time, saves a step compared to creating the database first and adding the filegroup afterward with a separate ALTER DATABASE statement, which matters most when you already know in advance how a database's data should be organized before the first row is ever inserted into it.

Older material on filegroups sometimes mentions assigning large object data of type text, ntext, or image to a specific filegroup. Those three types have been deprecated since SQL Server 2005, and current SQL Server guidance is to use varchar(max), nvarchar(max), and varbinary(max) instead, which support the same kind of large, variable-length data without relying on types Microsoft has long since recommended against for new development. A filegroup, and the FILESTREAM filegroup type covered above specifically, remains just as relevant for that kind of large object data under the modern types as it ever was under the deprecated ones.

With five different filegroup types covered across this lesson, three ordinary and two special-purpose, a single reference table pulls them together:

Type	Role
Primary	Holds the primary data file and every system table; exists in every database and can never be removed
Default	Receives any new object that doesn't name a filegroup explicitly; exactly one filegroup holds this role at a time
User-defined	Any additional ordinary filegroup created for performance, archival separation, or general organization
FILESTREAM	Stores large binary data as file-system files rather than inside the database's own data file
MEMORY_OPTIMIZED_DATA	Backs In-Memory OLTP tables; limited to one per database

A database can mix and match freely among these, well beyond the single primary filegroup every database starts with: an ordinary user-defined filegroup for archived data, a FILESTREAM filegroup for document attachments, and a MEMORY_OPTIMIZED_DATA filegroup for a handful of performance-critical tables can all coexist in the same database, each handling the specific job it's actually suited for.

Individually, each statement in this lesson is simple. Realistically, setting up and eventually retiring an archive filegroup involves several of them run in sequence, each building on the last. Here's the whole lifecycle for SalesArchive, start to finish:

-- 1. Create the filegroup and its file
ALTER DATABASE SalesDB
ADD FILEGROUP SalesArchive;

ALTER DATABASE SalesDB
ADD FILE
(
    NAME = N'SalesArchiveData',
    FILENAME = N'C:\SQLData\SalesArchive.ndf',
    SIZE = 128MB,
    MAXSIZE = 1024MB,
    FILEGROWTH = 64MB
)
TO FILEGROUP SalesArchive;

-- 2. Place a table on it
CREATE TABLE dbo.ArchivedOrders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL
) ON SalesArchive;

-- 3. Once the data has settled, mark it read-only
ALTER DATABASE SalesDB
MODIFY FILEGROUP SalesArchive READ_ONLY;

-- 4. Confirm the result
SELECT fg.name, fg.is_read_only, df.physical_name
FROM sys.filegroups AS fg
JOIN sys.database_files AS df
    ON df.data_space_id = fg.data_space_id
WHERE fg.name = 'SalesArchive';

Each step builds directly on the ones covered earlier in this lesson: creating the filegroup and file uses the same syntax as the previous lesson's filegroup example, placing the table uses the ON clause from earlier in this one, and the verification query joins sys.filegroups to sys.database_files to confirm both the filegroup's read-only status and the physical file backing it in one result, rather than checking each catalog view separately. That's the shape most real filegroup work actually takes: not one statement in isolation, but a short, deliberate sequence that takes a filegroup from nonexistent to fully set up and settled into its final role.

The next lesson covers how to delete a database entirely.