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:

CREATE TABLE [dbo].[BinaryDataTest2](
	[DataName] [nchar](10) NOT NULL,
	[Data] [varbinary](max) FILESTREAM NULL,
	[Data2] [nchar](10) 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:

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

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

(found here on MSDN)

Finally, add a file group with FILESTREAM enabled:

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:

CREATE TABLE [dbo].[BinaryDataTest](
	[ROWGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
	[DataName] [nchar](10) NOT NULL,
	[Data] [varbinary](max) FILESTREAM NULL,
	[Data2] [nchar](10) NULL
) ON [PRIMARY]

Finally, insert some data into your table:

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.