Default FileStream filegroup is not available in database 'DatabaseName'

With SQL Server 2012, a new table type FileTable is introduced for SQL Server developers and administrators. FileTable is a feature developed based on the FileStream feature that was introduced with Microsoft SQL Server 2008.

To create FileTable is easy but has some prerequisites. Before completing these prerequisites if t-sql developers try to create FileTable using following CREATE TABLE command:


CREATE TABLE ImagesTable AS FileTable

-- or

CREATE TABLE ImagesTable AS FileTable

WITH (

 FileTable_Directory = 'ImagesDirectory',

 FileTable_Collate_Filename = database_default

)

Code

The following SQL Engine error might happen

Msg 1969, Level 16, State 1, Line 1
Default FILESTREAM filegroup is not available in database 'DatabaseName'

If you open database properties and display FileGroups tab you will see no filegroup is created or no filegroup is defined as default FileStream filegroup. As you can see in the below screenshot, in the lower section of the screen which is titled as "FILESTREAM" there is no filegroup defined.

Default FILESTREAM filegroup is not available in database 'DatabaseName'

You can create a new filegroup in the FILESTREAM section and mark it as default

add-new-filegroup-as-database-default-filestream-filegroup

After filegroup is created, it is time to add a new database file with file type is FileStream Data. The default filestream filegroup will be automatically assigned.

You can change filegroup assignment to a non-default filegroup if you wish from the displayed combobox.

One last step is configuring the Path for the database file.

This will be a physical file folder where the FileStream database file will be stored.

add-filestream-data-filetype-database-file-for-filestream

After database file is created, the contents of the filestream database file folder be as follows:

filestream-filegroup-database-file-contents

At this step, if you try to create FileTable using "CREATE TABLE ImagesTable AS FileTable" sql command, you will get the following SQL exception

Msg 33414, Level 16, State 1, Line 2
FileTable objects require the FILESTREAM database option DIRECTORY_NAME to be non-NULL. To create a FileTable in the database 'FileDatabase', set the DIRECTORY_NAME option to a non-NULL value using ALTER DATABASE. Or, to set the DIRECTORY_NAME option to NULL, in the database 'FileDatabase' disable or drop the existing FileTables.

As the error indicates, the database option Directory Name has not been set yet. Directory Name can be configured via SQL Server Management Studio or using ALTER DATABASE command.

If you use SSMS, open database properties window and switch to database options tab

configure-database-filestream-directory-name-and-non-transacted-access

Now a new directory is created for the database FileStream data in the SQL Server FileStream network share

Now we can create a FileTable in SQL Server 2012 database


CREATE TABLE ImagesTable AS FileTable

WITH (

 FileTable_Directory = 'ImagesDirectory',

 FileTable_Collate_Filename = database_default

)

Code

Now you can copy and paste images directly into FileTable directory

copy paste into SQL Server FileStream FileTable directory

You can even execute a SQL SELECT statement over the new FileTable ImagesTable in order to see the manually copied image file is stored within the FileTable

SELECT * FROM ImagesTable

Code

select from Filetable table type in SQL Server 2012