MS SQL 2012 FileTable for Existing Instance

In order to use FileTables, the following four steps must be completed, in order:

1. Enable FileStream at the instance Level

2. Provide a FileStream Filegroup

3. Enable Non-Transactional access and specify FileTable directory at the Database Level

4. Create a FileTable

ALTER DATABASE 
ADD FILEGROUP 
CONTAINS FILESTREAM
GO
FileGroup
-- D:FileTableContainedTestDb must exist
ALTER DATABASE 
ADD FILE
(NAME = 'YourName' , FILENAME = 'D:FileTableContainedTestDbFS')
TO FILEGROUP 
GO
FilesDb
ALTER DATABASE DbInstanceName 
SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'MyDirectory' ) WITH NO_WAIT 
GO
FileStream_Options
CREATE TABLE DocumentStore AS FileTable 
WITH ( 
FileTable_Directory = 'DocumentTable', 
FileTable_Collate_Filename = database_default 
); 
GO

-- Note only works when Filestream Group is default

2 comments

  1. thanks! Helps me a lot!🙂

    1. No problem

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: