Last Updated: February 25, 2016
·
514
· dazfuller

Where does your SQL FILESTREAM data live?

Recently I needed to find out where physically on my server the FILESTREAM data was being kept for a specific table. Not having access to the DBA who set it up and SSMS proving less than adequate for the task I put a SQL script together to work it out as there seem to be a number of people who want to know this and most responses give you a UNC path, which is not what I wanted.

SELECT      t.name AS 'table',
            c.name AS 'column',
            fg.name AS 'filegroup_name',
            dbf.type_desc AS 'type_description',
            dbf.physical_name AS 'physical_location'
FROM        sys.filegroups fg
INNER JOIN  sys.database_files dbf
ON          fg.data_space_id = dbf.data_space_id
INNER JOIN  sys.tables t
ON          fg.data_space_id = t.filestream_data_space_id
INNER JOIN  sys.columns c
ON          t.object_id = c.object_id
AND         c.is_filestream = 1

http://ignoringthevoices.blogspot.co.uk/2014/07/where-does-your-filestream-data-live.html

Interestingly the problem turned out to be firewall and setup related in the end as the system had NetBIOS disabled (because why wouldn't you), but without NetBIOS FILESTREAM doesn't work if your app server and client a physically separated.