Create a FILESTREAM-Enabled Database
Applies To: SQL Server 2016
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).
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:
Arch1 are regular filegroups that cannot contain FILESTREAM data.
FileStreamGroup1 is the
CREATE DATABASE Archive ON PRIMARY ( NAME = Arch1, FILENAME = 'c:\data\archdat1.mdf'), FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3, FILENAME = 'c:\data\filestream1') LOG ON ( NAME = Archlog1, FILENAME = 'c:\data\archlog1.ldf') GO
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.
For existing databases, you can use the ALTER DATABASE statement to add a FILESTREAM filegroup.