Creating a FileStream Enabled Database


This article brings together a number of resources listed in the TechNet resource Getting Started with FILESTREAM Storage.

To create a FILESTREAM enabled database you need to make sure that the FILESTREAM is enabled. When you have made sure that the FILESTREAM is enabled, then you can create the database. After you have created the database, the final step is to create the table to hold the FILESTREAM data.

Other things you will need to consider include security privileges, detatching and attaching the database, and backing up and restoring the database.

Enabling the FILESTREAM

To enable the FILESTREAM execute the following command:

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

Creating a FILESTREAM-Enabled Database

Once you have configured your database server by enabling the FILESTREAM, you can create your FILESTREAM-enabled database using the script:

CREATE DATABASE FileStreamDB
ON
PRIMARY ( NAME = FileStreamDB,
FILENAME = 'C:\Data\FileStreamDB.mdf'),
FILEGROUP FileStreamGroup CONTAINS FILESTREAM(NAME = FileStreamData,
FILENAME = 'C:\Data\FileStream')
LOG ON ( NAME = FileStreamLog,
FILENAME = 'C:\Data\FileStreamLog.ldf')
GO

Creating a FILESTREAM Table

Now that you have created the database, you can create your tables. A table column that stores FILESTREAM data is made up of a VARBINARY(MAX) data type with the FILESTREAM attribute.

CREATE TABLE FileStreamDB.dbo.FileData
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[Description] NVARCHAR(255),
[Document] VARBINARY(MAX) FILESTREAM NULL
)
GO

The Id colum is a ROWGUIDCOL data type that is required to use FILESTREAM data with Win32 APIs. The Description column is an NVARCHAR(255) data type that is used to describe the file being stored. And the Document field is a VARBINARY(MAX) data type used to store the document in the file system.

Transact-SQL Manipulation

To insert data into a FILESTREAM database table use the T-SQL command:

INSERT INTO FileStream.dbo.FileData VALUES (newid (), "Test File", "C:\Data\Sales\Invoices\May 2011\Client Company X.xls");
GO

To update data into a FILESTREAM database table use the T-SQL command:

UPDATE FileStreamDB.dbo.FileData
SET [Document] = CAST('C:\Data\Sales\Invoices\May 2010\Client Company X.xls' as varbinary(max))
WHERE [Id] = 'D315D532-A0EC-40EA-B8B1-9A222A1F91D1';
GO

To delete a record in a FILESTREAM database use the T-SQL command:

DELETE FileStreamDB.dbo.FileData
WHERE [Id] = 'D315D532-A0EC-40EA-B8B1-9A222A1F91D1';
GO

UPDATE:

Microsoft provides an interesting article called FILESTREAM Data in SQL Server 2008 (ADO.NET) with code samples in C#.

Advertisements