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'),
CONTAINS FILESTREAM( NAME = FileStreamFTS_FileGroup,
FILENAME = 'd:\data\FileStreamFTS_FileGroup')
LOG ON ( NAME = FileStreamFTS_Log,
FILENAME = 'd:\data\FileStreamFTS_Log.ldf')
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
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
(NAME = 'ArcaneFS_Group'
, FILENAME = 'D:\Data\ArcaneCodeFS'
TO FILEGROUP ArcaneFileStreamGroup1
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.
3 thoughts on “Enabling FILESTREAM In The Database”
“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.”
What I can not believe!
Very nice example…. really good to understand
Thanks! is exactly what I was looking for