Create a Filestream In SQL Server 2008 In Three Easy Steps

Step 1 : Enable FileStream

The first step is to enable the filestream capabilities on your SQL Server instance. This can be found by going to your server properties in your SQL Server Configuration Manager and enabling the filestream there. Microsft explains how here.

Step 2 : Creating a FileStream Database

The following script will create a filestream database for us to use.

CREATE DATABASE Attachments
ON
PRIMARY ( NAME = Attachments,
    FILENAME = 'c:\data\Attachments.mdf'),
FILEGROUP FileStreamGroupAttachments CONTAINS FILESTREAM( NAME = AttachmentsFileStream,
    FILENAME = 'c:\data\Attachmentsfilestream')
LOG ON  ( NAME = Attachments_Log,
    FILENAME = 'c:\data\Attachments_Log.ldf')
GO

Now that we have our database, let’s create our table.

Step 3 : Creating a Table Which Uses FileStream

In the following example, I still create my Primary key column, but I also include an id column for the file stream since it requires a GUID column.

CREATE TABLE Attachments.dbo.Attachments
(
    [AttachmentID] [bigint] IDENTITY(1,1) NOT NULL,
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
    [Name] varchar(128),
    [AttachmentItem] VARBINARY(MAX) FILESTREAM NULL
)
GO

That’s all there is to it. Good luck and happy coding!

If you would like to know more about implementing in code, here is an example provided by microsoft in C# here.To use in the entity framework, Guy Burstein wrote  a nice little article here.