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.