Australian cloud for business software — built and run in Sydney since 2003

+61 2 8282 5656
support@netleverage.com
Talk to an engineer
Talk to an engineer
NetLeverage
NetLeverage

FILESTREAM and FileTable databases: backing up the files that live outside the MDF

Technical article
Backup & Recovery

Originally published:

About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

Most SQL Server databases sit in a data file and a log file. Some also have a folder full of ordinary files: scanned documents, images or attachments that an application stores through a feature called FILESTREAM, or a Windows share exposed through FileTables. Those files belong to the database, but they are not inside the MDF.

That difference catches recovery plans out. This guide explains where FILESTREAM data lives, what native SQL Server backup and restore do with it, where the documented gaps are, and how to find these databases without changing anything.

Why it matters

When a database's size looks small but the application holds years of documents, FILESTREAM is one possible reason (our reading). A recovery plan sized from the MDF, a file-copy job that skips a folder it cannot read, or a restore that leaves out a filegroup can return the tables without the documents they point to (our reading). Microsoft's documentation also lists features that do not support FILESTREAM at all, which affects which recovery options exist.

Where FILESTREAM data lives

Microsoft Learn describes FILESTREAM as integrating "the SQL Server Database Engine with an NTFS or ReFS file systems by storing varbinary(max) binary large object (BLOB) data as files on the file system." The feature page applies to SQL Server on Windows.

The files sit in a special filegroup: "A FILESTREAM filegroup is a special filegroup that contains file system directories instead of the files themselves. These file system directories are called data containers." A filegroup can hold several containers.

Three points from the documentation matter for recovery:

  • Enabling is instance-level as well as database-level. "FILESTREAM isn't automatically enabled when you install or upgrade SQL Server." It is set in SQL Server Configuration Manager and with filestream_access_level, and the enable page says to restart the SQL Server service. A rebuilt server will not have it until someone sets it again (our reading).
  • The container is SQL Server's. "Only the account under which the SQL Server service account runs is granted permissions to the FILESTREAM container. We recommend that no other account is granted permissions on the data container."
  • Hands off the folder. "Don't add or remove files in the FILESTREAM folder manually or through other applications. If you do, this will result in backup and inconsistency errors."

FileTables in one paragraph

"The FileTable feature builds on top of SQL Server FILESTREAM technology." A FileTable shows its files through a Windows share so that ordinary applications can open and save them, while the data stays in the database. Microsoft says FileTables are "integrated with SQL Server management tools, and features such as backup." The directory tree "surfaced through the share is a purely logical directory structure that is maintained within the FileTable."

What native backup does

The FILESTREAM overview is direct: "you can use all backup and recovery models with FILESTREAM data, and the FILESTREAM data is backed up with the structured data in the database." The FileTable management page repeats this: "When you use SQL Server to back up a FileTable, the FILESTREAM data is backed up with the structured data in the database."

The overview says partial backup can exclude FILESTREAM filegroups, but the precise partial-backup rules narrow that statement. For a read-write database, a partial backup contains the primary filegroup and every read-write filegroup; eligible read-only filegroups may be omitted. READ_WRITE_FILEGROUPS cannot arbitrarily leave out a writable FILESTREAM group. Selected file/filegroup backups are a different scope, and a partial restore sequence can also omit groups that exist in other backups. Audit capture scope and restore scope separately.

File-copy and agent-based tools

Because the container is a folder permissioned to the SQL Server service account, a file-level copy run under another account may fail to read it (our understanding). A copy that does read it captures files that may not match the database at that moment, because the engine manages the folder transactionally (our understanding). The documented route is a SQL Server backup, or a tool that uses SQL Server's own backup interfaces (our recommendation). If your backup product claims FILESTREAM support, ask how it captures the containers and test a restore.

Garbage collection and the log

Deleted or replaced FILESTREAM files are not removed from the container straight away. The garbage collector deletes them later. Microsoft's page for the stored procedure that forces garbage collection lists reasons files may remain unprocessed, including "Files that need to be pinned down because a log backup or checkpoint hasn't been taken" and "Files in the FULL or BULK_LOGGED recovery model."

In practice, a container that keeps growing under the full recovery model may point to missing or failing log backups, among other causes (our reading). This is a sign to investigate, not proof of a fault.

What restore needs

FILESTREAM enabled on the target. Before using FILESTREAM, "you must enable FILESTREAM on the instance of the SQL Server Database Engine." A disaster recovery server should be checked for this setting in advance (our recommendation).

Transfer size. The RESTORE arguments page notes: "When the database has configured FILESTREAM, or includes or In-Memory OLTP File Groups, MAXTRANSFERSIZE at the time of restore should be greater than or equal to what was used when the backup was created."

FileTable directory names. FILESTREAM ( DIRECTORY_NAME = directory_name ) sets the database-level directory, which "should be unique among all the database-level FILESTREAM directory names in the SQL Server instance." Restoring a second copy of the same database on one instance therefore needs a different name (our reading).

Partial restores. The same page warns: "If a partial restore sequence excludes any FILESTREAM filegroup, point-in-time restore is not supported." Forcing it with CONTINUE_AFTER_ERROR means "the partial restore sequence succeeds and the FILESTREAM filegroup becomes unrecoverable." That is a permanent outcome for that restored copy, so plan partial restores carefully.

Moving a database. Microsoft's procedure for moving a FILESTREAM-enabled database detaches it, moves both the files and the container folders, and attaches it with each location named. The container is part of the database in that procedure; leaving it behind leaves part of the data behind (our reading).

FileTable non-transactional access and point-in-time restore

FileTables can allow Windows applications to write files without a database transaction. The FileTable compatibility page says: "Windows applications don't understand database transactions, so Windows write operations don't provide the ACID properties of a database transaction. Therefore transactional rollbacks and recovery aren't possible with Windows update operations."

For backups, the FileTable management page says: "When non-transactional access is not enabled at the database level, these tools and operations work with full transactional consistency." It then warns that when full non-transactional access is enabled, "a FileTable could contain data that was updated more recently (through a non-transactional update) than the transaction that the tool or process is reading from the transaction log." A point-in-time restore may therefore return some files newer than the chosen point (as the page explains), which matters if you restore to a time just before a change.

Features that do not support FILESTREAM

From the compatibility pages:

  • Database snapshots: "SQL Server does not support database snapshots for FILESTREAM filegroups."
  • Database mirroring: "Database mirroring does not support FILESTREAM."
  • Log shipping: "Log shipping supports FILESTREAM", with FILESTREAM enabled on both servers.
  • Availability groups and FileTables: "FileTable functionality is partially supported by Always On availability groups." After failover, FileTable data "isn't accessible on readable secondary replicas."
  • Replication and FileTables: replication and related features, including change tracking, "aren't supported with FileTables."

Read-only checks

These queries read configuration and catalogue views and change nothing. Run them in SQL Server Management Studio or sqlcmd (Transact-SQL), in each database where noted. Record permission errors, which mean the list is incomplete.

Is FILESTREAM enabled on this instance?

SELECT SERVERPROPERTY('FilestreamConfiguredLevel') AS configured,
       SERVERPROPERTY('FilestreamEffectiveLevel') AS effective,
       SERVERPROPERTY('FilestreamShareName') AS share_name;

Microsoft notes the effective level "can be different than the FilestreamConfiguredLevel if the level has changed and either an instance restart or a computer restart is pending." Run the same query on any standby or recovery server.

Which databases have FILESTREAM containers, and where? Run in each database:

SELECT DB_NAME() AS database_name, name, physical_name,
       size AS used_size_8kb_pages
FROM sys.database_files
WHERE type_desc = 'FILESTREAM';

For FILESTREAM containers, size reflects current used container size in 8-KB pages, not maximum capacity. The alias makes that unit explicit. Multiply the page count by 8,192 using a sufficiently wide numeric type to obtain bytes, then convert to your stated GB or GiB unit before comparing with the recovery plan. Do not compare the raw page count directly with a capacity stated in gigabytes.

Is non-transactional FileTable access enabled?

SELECT DB_NAME(database_id) AS database_name, directory_name,
       non_transacted_access_desc
FROM sys.database_filestream_options;

Do recent backups include the FILESTREAM filegroups? Look at your backup job definitions for READ_WRITE_FILEGROUPS, FILEGROUP = or FILE = clauses. Any of these on a FILESTREAM database deserves a check that the containers are covered (our recommendation).

These checks are partial. They show configuration, not whether a given backup file restores with its documents intact (our reading).

Safe restore rehearsal

Use an authorised, isolated test instance with FILESTREAM enabled and a synthetic database (our recommendation).

  1. Create a test database with a FILESTREAM filegroup and a table of sample files; record each file's SHA-256 hash, for example with Get-FileHash in PowerShell on files exported through the application or a query.
  2. Back up the full database, add more rows, and take a log backup.
  3. Restore to a second test instance with FILESTREAM enabled, using MOVE for the container. Export the files and compare hashes.
  4. Repeat on an instance with FILESTREAM disabled and record the error, so the team recognises it.
  5. If you use a partial or selected-filegroup strategy, first document the valid capture scope. A read-write database partial backup includes all writable groups; a read-only FILESTREAM group may be excluded where the partial-backup rules allow it. In the isolated copy, rehearse the actual supported restore sequence and record the effect of any group deliberately left out. Do not infer that READ_WRITE_FILEGROUPS can omit an arbitrary writable group.

Diagnosing what you find

  • Restore succeeds but documents are missing: a selected-filegroup backup omitted the group, an eligible read-only group was omitted from a partial backup, the restore sequence left a group out, or the application stores some documents outside the database.
  • Restore fails on FILESTREAM: FILESTREAM is not enabled on the target; a restart is pending after enabling it; or the container path given in MOVE is not valid.
  • Container larger than expected: garbage collection is waiting for log backups or checkpoints; a long-running transaction is open; or the data has genuinely grown.
  • Backup reports consistency errors: files were added or removed in the container by hand or by another program; antivirus or another filter interfered; or storage damage occurred.

Have a qualified DBA review the plan before applying any of it to production.

Worked example (illustrative)

The following is invented. A practice-management database is listed as 40 GB in the recovery plan. The read-only query shows a FILESTREAM container using 900 GB for scanned letters. The nightly job was written years ago as a selected-filegroup backup naming only the primary and data filegroups, not a READ_WRITE_FILEGROUPS partial backup; the FILESTREAM filegroup was added later and never added to the job.

The team rehearses a restore of the latest backup on a test instance. The tables come back, but the letters are missing, because no backup in the chain contains the FILESTREAM filegroup. They take a full backup straight away, change the job to back up the whole database, correct the size in the recovery plan, and add the configuration query to the standby server checklist.

Risks and limits

  • FILESTREAM and FileTables apply to SQL Server on Windows per the pages cited.
  • Behaviour of third-party backup tools with FILESTREAM varies; this guide does not assess any product.
  • No query, backup or restore in this guide was run in a lab. Test in an isolated environment first.

Sources

All accessed 14 November 2026.

Related future topics

  • Piecemeal restores and read-only filegroups
  • Sizing recovery time for databases with large document stores
  • Standby server configuration drift checks for SQL Server

This article was originally published on .