Configure and Enable Filestream at Server Instance Level Using GUI
- Connect to SQL Server 2008 Instance using SQL Server Management Studio
- In Object Explorer, right click the server and select properties to view the Server Property window
- Under Advanced, Click on the drop down next to Filestream Access Level and select Full access enabled
- To do this the user needs to be a member of the sysadmin or serveradmin fixed server role to change the default settings for Filestream
- Click Ok to save changes
How to create a Filestream Database
The FILESTREAM feature uses a special type of filegroup when you create the database. You need to specify the CONTAINS FILESTREAM clause for at least one filegroup in the create database statement.
Use Master
Go
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’FileStreamDB’)
DROP DATABASE FileStreamDB
GO
USE Master
GO
CREATE DATABASE FileStreamDB ON PRIMARY
( NAME = FileStreamDB_Data,
FILENAME = N’D:FileStreamFileStreamDB_Data.mdf’,
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 15%),
FILEGROUP MyDBData
( NAME = MyFileStream_Data,
FILENAME = N’D:FileStreamFileStreamDB_Data.ndf’,
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB),
FILEGROUP FileStream CONTAINS FILESTREAM
( NAME = FileStream,
FILENAME = N’D:FileStreamFileStreamData’)
LOG ON
( NAME = ‘FileStreamDB_Log’,
FILENAME = N’D:FileStreamFileStreamDB_Log.ldf’,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB);
GO
The image below shows the properties of FileStreamDB Database which was created by executing the above TSQL statement:
The below snippet displays the files which got created for FileStreamDB database in D:FileStream folder. You will see a folder named FileStreamData which will store all the varbinary (max) values.
Within the D:FileStreamFileStreamData folder you can see a sub folder named $FSLOG and a file named filestream.hdr, this file is a header file for the FILESTEAM container.