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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: