Monday, November 9, 2015

SQL Server 2012 : Working with FileTables

Hi Guys,

I had a requirement to store files in the database. But had an issue with storage as the files were growing, so my project leader asked me to come up with a solution. Then I found about FileStream and FileTables. So thought of sharing my experience.

1) Enable FileStream in your instance
Go to Configuration Manager, select the instance -> Properties -> FileStream Tab



2) Enable FileStream on your Server
Go to SQL Server Management Studio, select server -> Properties -> Advanced ->FileStream -> FileStream Access Level -> Full Access Enabled 




3) Create Database with FileStream

CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'd:\FileStorage\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'd:\FileStorage\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = 'd:\FileStorage\archlog1.ldf')
GO


4) Specify FileStream Directory Name
Go to SQL Server Management Studio, select database -> Properties -> Options ->FileStream -> FileStream Directory Name  -> Provide Any Name



5) Create FileTable 

USE Archive

IF OBJECT_ID('dbo.TestFileTable', 'U') IS NOT NULL
  DROP TABLE dbo.TestFileTable
GO

CREATE TABLE dbo.TestFileTable AS FILETABLE
  WITH
  (
    FILETABLE_DIRECTORY = 'ArchiveFileTable', --This name must be the name given in 3rd Step
    FILETABLE_COLLATE_FILENAME =  database_default
  )
GO

6) Add Files to your FileTable

There are three ways you can do this.

  • Drag and drop files from the source folders to the new FileTable folder in Windows Explorer.
  • Use command line options such as MOVE, COPY, XCOPY, or ROBOCOPY from the command prompt or in a batch file or script.
  • Write a custom application in C# or Visual Basic.NET that uses methods from the System.IO namespace to move or copy the files.




7) Insert Files to your FileTable using TSQL

INSERT INTO [dbo].[TestFileTable]([name],[file_stream])SELECT'NewFile.txt', * FROM OPENROWSET(BULK N'd:\NewFile.txt'SINGLE_BLOB)AS FileData
GO