Create a FILESTREAM-Enabled Database
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
This topic shows how to create a database that supports FILESTREAM. Because FILESTREAM uses a special type of filegroup, when you create the database, you must specify the CONTAINS FILESTREAM clause for at least one filegroup.
A FILESTREAM filegroup can contain more than one file. For a code example that demonstrates how to create a FILESTREAM filegroup that contains multiple files, see CREATE DATABASE (SQL Server Transact-SQL).
To create a FILESTREAM-enabled database
In SQL Server Management Studio, click New Query to display the Query Editor.
Copy the Transact-SQL code from the following example into the Query Editor. This Transact-SQL code creates a FILESTREAM-enabled database called Archive.
For this script, the directory C:\Data must exist.
To build the database, click Execute.
The following code example creates a database that is named Archive. The database contains three filegroups: PRIMARY, Arch1, and FileStreamGroup1. PRIMARY and Arch1 are regular filegroups that cannot contain FILESTREAM data. FileStreamGroup1 is the FILESTREAM filegroup.
For a FILESTREAM filegroup, FILENAME refers to a path. The path up to the last folder must exist, and the last folder must not exist. In this example, c:\data must exist. However, the filestream1 subfolder cannot exist when you execute the CREATE DATABASE statement. For more information about the syntax, see CREATE DATABASE (SQL Server Transact-SQL).
After you run the previous example, a filestream.hdr file and an $FSLOG folder appears in the c:\Data\filestream1 folder. The filestream.hdr file is a header file for the FILESTREAM container.
The filestream.hdr file is an important system file. It contains FILESTREAM header information. Do not remove or modify this file.
For existing databases, you can use the ALTER DATABASE statement to add a FILESTREAM filegroup.