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:
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.
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.