How to create a file stream on a database.

Problem: When to create a file stream, how to create file stream and assign file groups to the file stream data folder.

Solution: FILESTREAM (SQL Server) Starting SQL Server 2008

FILESTREAM is a feature which provides efficient storage of BLOB’s, management and streaming of unstructured data stored as files on the file system.

When to create filestream:

It is advisable to store BLOB data which is greater than 1 MB as FILESTREAM data for better performance. If the BLOB data size is smaller than 1 MB, then you will see better performance when it is stored within a database.

How to create filestream:

For creating a FILESTREAM enabled database; the Alter/Create needs to specify CONTAINS FILESTREAM clause for at least one of the FILEGROUPs within the create database statement.

Example:

In this Example, I would like to show we can create file stream on a database and assign a file group to the file stream.

DECLARE @DataFolder NVARCHAR(max)

DECLARE @db NVARCHAR(max)

DECLARE @statement NVARCHAR(max)

SET @DataFolder = N'C:\Temp\FileStream' -- this folder must exist

SET @db = N'Test'

IF NOT EXISTS (SELECT 1 FROM sys.filegroups WHERE NAME = N'FILESTREAM_DATA')

BEGIN

                SET @statement = N'ALTER DATABASE ' + @db + ' ADD FILEGROUP FILESTREAM_DATA CONTAINS FILESTREAM'

                EXECUTE dbo.sp_executesql @statement

                SET @statement = N'ALTER DATABASE ' + @db + '

                ADD FILE (NAME = ' + @db + '_FILESTREAM, FILENAME = ''' + @DataFolder + '\' + @db + '_FS'')

                TO FILEGROUP FILESTREAM_DATA'

                EXECUTE dbo.sp_executesql @statement

END

GO

Once finished executing this script check your Datafolder for the filestream.Hdr which has the header information about the filestream and should not be deleted or else the database will be corrupted.

You can also check to see the database properties for the file stream file group and location.