How to Create and Use Filestream in SQL Server: Step by Step

João Barros 03 de August de 2026 4 min read

This tutorial shows how to configure and use Filestream in SQL Server to store large files in the file system with transactional integrity. Filestream is useful when you need to store large BLOBs without bloating the database, while maintaining ACID and performance.

Prerequisites

SQL Server instance (Standard/Enterprise or Express with Filestream support).
Administrator permissions on the server to enable Filestream and create folders.
SQL Server Management Studio (SSMS) or access to sqlcmd.

Step 1: Enable Filestream at the server level

Filestream must be enabled at the instance level. This involves configuring the SQL Server startup option and enabling the Filestream service in Configuration Manager.

-- 1. Check status (run in SSMS):
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'filestream access level';

-- 2. Set access (0 = off, 1 = Transact-SQL, 2 = T-SQL + Win32):
EXEC sp_configure 'filestream access level', 2;
RECONFIGURE;

After executing this, open the SQL Server Configuration Manager and, for the instance, enable the Filestream service in the properties tab (if applicable) and restart the SQL Server instance.

Step 2: Create a Filestream Filegroup and folders on the file system

You need a Filestream filegroup that references a folder on the file system. First create the folder with appropriate permissions and then add the filegroup to the database.

-- Assuming a sample database:
CREATE DATABASE DemoFilestream
ON PRIMARY (
    NAME = DemoFilestream_Data,
    FILENAME = 'C:\SQLData\DemoFilestream.mdf'
), FILEGROUP FGFilestream CONTAINS FILESTREAM(
    NAME = DemoFilestream_FS,
    FILENAME = 'C:\SQLData\DemoFilestream_FS'
)
LOG ON (
    NAME = DemoFilestream_Log,
    FILENAME = 'C:\SQLData\DemoFilestream.ldf'
);

If the folder already exists, just ensure NTFS permissions for the SQL Server service account.

Step 3: Create a table with a Filestream column

Create a table that includes a VARBINARY(MAX) column with the FILESTREAM property and a ROWGUIDCOL column that will be used to identify each record (mandatory).

USE DemoFilestream;
GO
CREATE TABLE Documents (
    DocumentId UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
    FileName NVARCHAR(260) NOT NULL,
    FileContent VARBINARY(MAX) FILESTREAM NULL,
    UploadedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

Step 4: Insert and read files (example with T-SQL)

You can insert data using OPENROWSET(BULK...) to load files from disk into the FILESTREAM column. For reading, you can export or read directly with SELECT.

-- Insert a file into Filestream (example: C:\Upload\report.pdf)
INSERT INTO Documents (FileName, FileContent)
SELECT 'report.pdf', BulkColumn
FROM OPENROWSET(BULK 'C:\Upload\report.pdf', SINGLE_BLOB) AS x;

-- Read metadata and blob size
SELECT DocumentId, FileName, DATALENGTH(FileContent) AS SizeBytes, UploadedAt
FROM Documents;

-- Export file using T-SQL and additional command: use fn_varbintohexstr or external tools.

Note: to export to a file on the server you can use a client application or a PowerShell script that reads the column and writes to disk. You can also use the Filestream APIs with C# code for efficient Win32 access.

Step 5: Best practices and common errors

Use these recommendations to avoid common Filestream issues: backups, maintenance and permissions are critical.

Include the Filestream filegroup in FULL backups and RESTOREs; without this you will lose Filestream data.
Check NTFS permissions for the SQL Server service account on the Filestream folder.
Avoid storing small files (< 1 MB) in Filestream — for small files a regular VARBINARY may be simpler.
If you see errors like "The backup's filegroup does not contain the FILESTREAM data" verify that you backed up the correct filegroup.

Verify the result

To confirm Filestream is working, run a SELECT and check the size of the BLOBs and the existence of files in the filegroup folder (files do not appear with friendly names). Perform a BACKUP DATABASE and then a RESTORE in a test environment to ensure the filegroup was included. Quick verification example:

-- Check records and size
SELECT DocumentId, FileName, DATALENGTH(FileContent) AS SizeBytes
FROM Documents;

-- Back up (filegroups are included automatically if FULL)
BACKUP DATABASE DemoFilestream TO DISK = 'C:\Backups\DemoFilestream.bak';

Conclusion

Filestream in SQL Server allows efficient storage of large files while maintaining transactional integrity and improving performance. Next steps: try accessing via an application (C# or PowerShell) to read/write files directly, and test backup/restore scenarios. Tip: before production, test NTFS permissions and backup routines to avoid data loss.