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](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/91c45b04-ee60-477e-88b7-5ee27b358acb/filestream-column-in-sql-management-studio?forum=sqldatabaseengine) 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:

[![filestream1](http://pmichaels.net/wp-content/uploads/2016/01/filestream1-300x139.png)](http://pmichaels.net/wp-content/uploads/2016/01/filestream1.png)

https://msdn.microsoft.com/en-us/library/cc645923.aspx

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

[![filestream2](http://pmichaels.net/wp-content/uploads/2016/01/filestream2-300x234.png)](http://pmichaels.net/wp-content/uploads/2016/01/filestream2.png)

(found [here on MSDN](https://msdn.microsoft.com/en-us/library/cc645923.aspx))

Finally, add a file group with FILESTREAM enabled:

[code lang="SQL"]
ALTER DATABASE TestDB
ADD FILEGROUP fs\_fg\_filestream CONTAINS FILESTREAM  
GO  
ALTER DATABASE TestDB
ADD FILE  
(  
    NAME= 'filestream',  
    FILENAME = 'C:\\db\\fs' 
)  
TO FILEGROUP fs\_fg\_filestream  
GO  

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:

filestream3

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
Twitter

© Paul Michaels 2022