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?

Answer 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