Using the FILESTREAM data type in SQL Server 2008
SQL Server 2008 introduced a FILESTREAM data type to handle images, video and audio. The data is stored on a windows file share rather than in the database itself.
To enable FILESTREAM, you have to do three steps:
1) Go into SQL Server Configuration Manager and turn it on.
2) Create/modify your database to allow FILESTREAM data. You must have a filegroup that allows for FILESTREAM data.
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.
CREATE DATABASE Archive
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')
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.
3) Create a table with a column that is a varbinary(MAX) Filestream data type. The table with the filestream data will be created in the filestream file group.