Enabling FILESTREAM In The Database

Once you have your server configured for FILESTREAM, you will need to configure your database. The easiest way is to establish it when you first create the database.

CREATE DATABASE FileStreamFTS ON 

PRIMARY ( NAME = FileStreamFTS_Data,

    FILENAME = 'd:\data\FileStreamFTS_Data.mdf'),

FILEGROUP FileStreamFTS_FileGroup1 

  CONTAINS FILESTREAM( NAME = FileStreamFTS_FileGroup,

    FILENAME = 'd:\data\FileStreamFTS_FileGroup')

LOG ON  ( NAME = FileStreamFTS_Log,

    FILENAME = 'd:\data\FileStreamFTS_Log.ldf')

GO

In the above example, I created my database in a directory on my D drive named “D:\Data”. If you examine the folder you ’ll now see two files, the mdf and ldf files. There is also a folder “FileStreamFTS_FileGroup”. It is in this folder that your files will eventually be stored in.

You may be tempted to poking around in the folder, and perhaps even access the stored files directly. My advice to you: don’t. Microsoft has strongly advised against this practice. After all, you went to all the trouble of setting up FILESTREAM to let SQL Server handle this for you, so let it do the handling. If you are curious to dig deeper into how FILESTREAM works behind the scenes, I would recommend Paul Randal’s excellent white paper available at http://msdn.microsoft.com/en-us/library/cc949109.aspx.

The above example shows how to add FILESTREAM capability when you first create your database. That’s great, but what if you already have an existing database you want to add FILESTREAM to? I have just such an existing database, “ArcaneCode”. To add FILESTREAM I need to issue two ALTER DATABASE statements. First, we need to add a FILEGROUP and indicate that file group contains FILESTREAM objects.

ALTER DATABASE ArcaneCode

ADD FILEGROUP ArcaneFileStreamGroup1 

  CONTAINS FILESTREAM

GO

In the second we have to indicate to the FILEGROUP where the FILESTREAM should be stored. Pass in the directory name where you want your files saved as the FILENAME parameter. As with the create statement the directory can not exist, if it does you’ll get an error.

ALTER DATABASE ArcaneCode

ADD FILE  

  (NAME = 'ArcaneFS_Group'

   , FILENAME = 'D:\Data\ArcaneCodeFS'

   )

TO FILEGROUP ArcaneFileStreamGroup1 

GO

This completes the steps needed to create a database with FILESTREAM, or add it to an existing database. In the next lesson we will look at creating tables that use FILESTREAM.