Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

The Voice of the DBA

Steve Jones is the editor of and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at

Create a Filestream Filegroup for Filetables – SQL Server 2012

Once you’ve enabled filestream, the next step is to add a filegroup to your database to hold the filestream data. This is pretty easy to do, and I’ll show you the SSMS and code versions.

If you want to know more about these Filestream containers, you can read BOL. Let’s create a simple database:

-- create a new database
create database UnstructuredData

This is a simple database with my instance defaults in place. It has a single mdf, a single ldf, and the default Primary filegroup. Let’s not add a new filegroup:

-- add a filestream FG
ALTER DATABASE [UnstructuredData]

Here I am adding the filegroup (empty) and specifying this as a filestream container. You cannot mix Filestream data and non-Filestream data in the same filegroup in SQL Server 2012.

To add a file, we can use the ALTER DATABASE command:

-- Add a file to the Filestream FG
ALTER DATABASE [UnstructuredData] 
  ADD FILE ( NAME = N'UnstructuredFS', 
             FILENAME = N'c:\fs\UnstructuredFS' ) 

Here I am adding a file, which is actually a folder in this case. According to the documentation, the path up to the last folder (c:\fs in this case) must exist, but the last folder (UnstructuredFS) must not.

You could do all of this in one statement, as shown here:

CREATE DATABASE [UnstructuredData]
( NAME = N'UnstructuredData', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UnstructuredData.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
( NAME = N'UnstructuredFS', FILENAME = N'c:\fs\UnstructuredFS' , MAXSIZE = UNLIMITED)
( NAME = N'UnstructuredData_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UnstructuredData_log.ldf' , SIZE = 784KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

This gets you a space for holding your Filestream data. In 2012, you can now have more than one file for Filestream data, so you can separate out your filegroup across different physical locations if you have a need to do so for performance or scalability.

In the next post, I’ll build a FileTable and store some documents in it.

Filed under: Blog Tagged: Filestream, Filetable, sql server, syndicated


Leave a comment on the original post [, opens in a new window]

Loading comments...