Setting up SQL Server to use the FILESTREAM feature

February 05, 2016

Whilst playing about with this feature of SQL Server, I encountered the above error. This post should lead you around the error. It does not make any claims as to whether using the FILESTREAM feature is a good, or bad idea.

The error:

Msg 1969, Level 16, State 1, Line 14 Default FILESTREAM filegroup is not available in database ‘TestDB’.

The table create statement that caused this was:

[code lang=“SQL”] CREATE TABLE [dbo].[BinaryDataTest2]( [DataName] nchar NOT NULL, [Data] varbinary FILESTREAM NULL, [Data2] nchar NULL ) ON [PRIMARY]

I have to be honest, and say that I did, initially try to create this through the UI designer.  [this thread]( put me straight on that.

So, the next stage was to create a filegroup with the FILESTREAM enabled, but if you do that now, you'll (likely) get the following error:

> Msg 5591, Level 16, State 3, Line 1
> FILESTREAM feature is disabled.

This is a property of the SQL Server instance, not the DB:


Next, run SQL Configuration Manager and enable FILESTREAM here as well.


(found [here on MSDN](

Finally, add a file group with FILESTREAM enabled:

[code lang="SQL"]
    NAME= 'filestream',  
    FILENAME = 'C:\\db\\fs' 
TO FILEGROUP fs\_fg\_filestream  

Obviously, replace “C:\db\fs” with an actual location on your hard-drive.

The next error I got was:

A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

Okay, so you need to assign a field at a unique identifier:

[code lang=“SQL”] CREATE TABLE [dbo].[BinaryDataTest]( [ROWGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE, [DataName] nchar NOT NULL, [Data] varbinary FILESTREAM NULL, [Data2] nchar NULL ) ON [PRIMARY]

Finally, insert some data into your table:

[code lang="SQL"]
INSERT INTO BinaryDataTest(ROWGUID, DataName, Data, Data2) 
Values (NEWID()
      , 'test'
      , Convert(varbinary,'test')
	  , null

If you have a look at your (equivalent of) “c:\db”, you’ll see exactly what the effect of this was:


Basically, you are now storing the data from the DB in the file system. I re-iterate, I make no claims that this is a good or bad thing, just that it is a thing.

Profile picture

A blog about one man's journey through code… and some pictures of the Peak District

© Paul Michaels 2022