This topic explains how to create a database that supports FILESTREAM. Because FILESTREAM uses a special type of filegroup, the CONTAINS FILESTREAM clause must be specified for at least one filegroup when the database is created.
Creating a FILESTREAM-enabled database
1. In SQL Server Management Studio, click New Query to display the Query Editor.
2. Copy the Transact-SQL code for the following example into the Query Editor. This Transact-SQL code can create a FILESTREAM-enabled database called Archive.
Note: For this script, the C:\Data directory must exist.
3. To build your database, click Execute.
Sample
The following code example creates a database named Archive. The database contains three filegroups: PRIMARY, Arch1, and FileStreamGroup1. PRIMARY and Arch1 are general filegroups that cannot contain FILESTREAM data. FileStreamGroup1 is a FILESTREAM file group.
Sql
Copy Code code as follows:
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
For the FILESTREAM filegroup, FILENAME refers to a path. The path to the last folder must exist, but the last folder cannot exist. In this example, the C:\Data must exist. However, the Filestream1 subfolder cannot exist when the CREATE DATABASE statement is executed. For more information about this syntax, see CREATE DATABASE (Transact-SQL).
After running the example above, the Filestream.hdr file and $FSLOG folder will appear in the C:\Data\filestream1 folder. The Filestream.hdr file is the header file for the FileStream container.
Important Tips
The Filestream.hdr file is an important system file. It contains FILESTREAM header information. Do not delete or modify this file.
For an existing database, you can use the ALTER database statement to add a FILESTREAM filegroup.
