Physical location of FILESTREAM data

Question

How could I know the physical location (so I can see it in Windows Explorer) path of a FILESTREAM data that I've just inserted into DB?

Solution 1

There is one option for this: method PhysicalPathName(). If you are on SQL Server 2012 or upper now, this code will work for you:

SELECT stream.PhysicalPathName() AS 'Path' FROM Media
OPTION (QUERYTRACEON 5556)

For SQL Server 2008/2008 R2 you will need to enable trace flag 5556 for the whole instance:

DBCC TRACEON (5556, -1)
GO

or for the particular connection in which you are calling PhysicalPathName() method:

DBCC TRACEON (5556, -1)
GO

Solution 2

I know this is an older post but as it still comes up high in the Google search rankings I thought I'd post an answer. Certainly in later versions of SQL (I've not tried this on 2008) you can run the following query:

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

Source

Solution 3

As Pawel has mentioned, it is not a good idea to access the FILESTREAM files using Windows Explorer. If you are still determined to go ahead and explore this, the following tip might help.

The FILESTREAM file names are actually the log-sequence number from the database transaction log at the time the files were created. Paul Randal has explained it in this post. So One option is to find out the log sequence number and look for a file named after that in the file stream data container.