Friday, February 28, 2014

How to Import and Export Unstructured Data in SQL Server - FILESTREAM

A few weeks ago I posted an article on how to import and export unstructured data in SQL Server 2005 by using the "image" data type.

In this article I will show how you can manipulate unstructured data in SQL Server 2008 or later by using the FILESTREAM feature. FILESTREAM allows storing unstructured data (i.e. music, video, documents, etc.) onto the NTFS file system and manipulating it via the Database Engine.

Step 1: Enabling FILESTREAM
Before using the FILESTREAM feature you have to enable it. To this end you need to navigate to SQL Server Configuration Manager and under SQL Server Services right click on the SQL Server instance, select properties and in the “FileStream” tab check “Enable FILESTREAM for Transact-SQL access” and “Enable FILESTREAM for file I/O access”:

Figure 1: Enabling FILESTREAM

The last step for enabling FILESTREAM is from within SSMS, to open a new query window and execute the following T-SQL statement and then restart the SQL Server instance:

EXEC sp_configure filestream_access_level, 2;

Note: the available filestream access levels are:

  • 0: Disables FILESTREAM support for this instance.
  • 1: Enables FILESTREAM for Transact-SQL access.
  • 2: Enables FILESTREAM for Transact-SQL and Win32 streaming access.

Step 2: Creating a FILESTREAM-Enabled Database 
For creating a FILESTREAM-enabled database you just need to include a FILESTREAM filegroup. For example:

PRIMARY ( NAME = FileStreamDBData,
    FILENAME = 'C:\Blog\SQLData\filestreamDB_data.mdf'),
    FILENAME = 'C:\Blog\SQLData\filestream1')
LOG ON  ( NAME = FileStreamDBLogs,
    FILENAME = 'C:\Blog\SQLData\filestreamDB_log.ldf');

Step 3: Creating a Table for Storing FileStream Data
The only difference between a "normal" table and a table that can store filestream data is the use of the "FILESTREAM" data type for a specific column in the table's definition script:

USE [FileStreamDB];

[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, 
[FileName] VARCHAR(100),

Step 4: Storing FileStream Data
For this example, consider the following unstructured data file:
Figure 2: Image file to be stored in FILESTREAM-enabled database.

Now, let's store the file in our FILESTREAM-enabled database and table that was created earlier:
USE [FileStreamDB];

    VALUES (newid (), 
(SELECT * FROM   OPENROWSET(BULK 'C:\Testing\2\SampleImage.png',SINGLE_BLOB) AS x)

Here's the contents of the table:
Figure 3: The contents of the FILESTREAM-enabled table after inserting unstructured data (image file).

As you can see, the file is visible on the file system level too:
Figure 4: Binary file stored using FILESTREAM - Accessible on the file system level.
Now let's try to open the file using MS Paint: 

Figure 5: Accessing the data stored in the FILESTREAM-enabled database from the file system level (Windows).

As you can see, the image file is stored in the SQL Server database table but besides T-SQL access, you can also access it from Windows! 

What we just did with the above example, shows a small glimpse of the real power of FILESTREAM, that is leveraging the performance and rich APIs of the Windows file system and at the same time maintaining consistency between structured and unstructured data in SQL Server.

FILESTREAM actually works like a bridge between structured and unstructured data via a combination of transactional data and file system access and can be extremely useful in cases where you have many binary objects like images and videos and you want to store it in SQL Server and being able to access it with the speed of the file system.

Check out my latest eBooks on SQL Server:
Administering SQL Server - Ebook Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (


Nikodem DobrzaƄski said...

OK, I saw an import filestream data, but I didn't see an export filestream data at your post. Title is missing or you forgot add example how to export data from existing table, that used FILESTREAM.

Artemakis Artemiou [MVP] said...

Hi Nikodem,

Thank you for your comment.

When you use FileStream for storing binary files in SQL Server, you can access these files directly from the File System (see Figure 4) as well because they are actually stored in the File System. The files can be managed from SQL Server or directly from the File System. That's the main benefit of using FileStream.