How to Create a database with support for FILESTREAM

This topic describes how to create a database that supports FILESTREAM. Because FILESTREAM uses a special type of file group, you must specify the contains filestream clause for at least one file group when creating a database.

Create a database with FILESTREAM Enabled
1. in SQL Server Management Studio, click "new query" to display the query editor.
2. Copy the following example of Transact-SQL code to the query editor. This Transact-SQL code can create a database with FILESTREAM enabled, called Archive.
Note: For this script, the C: \ Data DIRECTORY must exist.
3. to generate a database, click execute ".

Example
The following code example creates a database named Archive. The database contains three file groups: PRIMARY, Arch1, and filestreamgroup1. PRIMARY and Arch1 are regular file groups that cannot contain FILESTREAM data. FileStreamGroup1 is a FILESTREAM file group.
SQL
Copy codeThe Code is 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 FILESTREAM file groups, FILENAME references a path. The path of the last folder must exist, but the last folder cannot exist. In this example, c: \ data must exist. However, when executing the create database statement, the filestream1 sub-Folder cannot exist. For more information about this syntax, see create database (Transact-SQL ).

After running the preceding example, the filestream. hdr file and the $ FSLOG folder will appear in the c: \ Data \ filestream1 folder. The filestream. hdr file is the header file of the FILESTREAM container.

Important
Filestream. hdr files are important system files. It contains the FILESTREAM header information. Do not delete or modify this file.
For existing databases, you can use the alter database statement to add FILESTREAM file groups.