MS SQL 2012 FileTable with new DB instance

Here’s a simple example which helps setup the new FileTable feature in a new database.
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO
-- Create Database
CREATE DATABASE FileTableDB
ON PRIMARY
(Name = FileTableDB,
FILENAME = 'D:\FileTable\FTDB.mdf'),
FILEGROUP FTFG CONTAINS FILESTREAM
(NAME = FileTableFS,
FILENAME='D:\FileTable\FS')
LOG ON
(Name = FileTableDBLog,
FILENAME = 'D:\FileTable\FTDBLog.ldf')
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'FileTableDB');
GO
-- Check the Filestream Options
SELECT DB_NAME(database_id),
non_transacted_access,
non_transacted_access_desc
FROM sys.database_filestream_options;
GO

FileTableNew

USE FileTableDB
GO
CREATE TABLE FileTableTb AS FileTable
WITH
(FileTable_Directory = 'FileTableTb_Dir');
GO
SELECT *
FROM FileTableTb
GO
\\<computerName>\mssqlserver2012\FileTableDB\FileTableTb_Dir
INSERT INTO [dbo].[FileTableTb]
([name],[file_stream])
SELECT
'NewFile.txt', * FROM OPENROWSET(BULK N'd:\NUnit-2.6.1.msi', SINGLE_BLOB) AS FileData
GO>

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: