How to Create a FILESTREAM Enabled SQL Server Database

To be able to create a FILESTREAM enabled SQL Server Database or enable FILESTREAM on an existing SQL Server Database, you need to Enable FILESTREAM on the SQL Server instance.

To create a FILESTREAM enabled database named FS_Database, that contains PRIMARY filegroup that cannot contain FILESTREAM data, and FileStreamFileGroup that is the FILESTREAM filegroup, run the following code:

USE master
GO
CREATE DATABASE FS_Database ON PRIMARY
( NAME = N'FS_Database', FILENAME = N'D:\Data\FS_Database.mdf',
SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB ),
FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
( NAME = FileStreamData, FILENAME = 'D:\Data\FileStreamData')
LOG ON
( NAME = N'FS_Database_log', FILENAME = N'D:\Data\FS_Database_log.ldf',
SIZE = 100MB , MAXSIZE = UNLIMITED , FILEGROWTH = 100MB)
GO

After you run this code, you will see the 'D:\Data\FileStreamData' folder that contains an important system file filestream.hdr and an $FSLOG folder. Do not modify or delete the filestream.hdr file.

Before you run the CREATE DATABASE statement from this example, 'D:\Data\' folder must exist, where the data files, log files and FILESTREAM folder will be created (FILENAME refers to a path: 'D:\Data\FileStreamData' for a FILESTREAM filegroup). Folder 'D:\Data\FileStreamData' must not already exist when you execute the CREATE DATABASE statement.
If the last folder already exists, you will get the following error:

Msg 5170, Level 16, State 2, Line 1
Cannot create file 'D:\Data\FileStreamData' because it already exists. Change the file path or the file name, and retry the operation.

cannot_create_file_because_it_already_exists_filestream