SQL Server 2008, Physical Location of FILESTREAM Data...

Question

hi,

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?

Thanks

Answer 1

First you need to understand that the FileStream is being stored on the server hosting your SQL Server 2008 database. If you have a DBA, ask them where they created it the FileStream at. Of course, you'll then need rights to the server to navigate it to see the directories. You won't be able to manipulate the files in any way either, but you will be able to see them. Most DBA's won't be keen on letting you know where the FileStream is located at.

However, you can get at the path by a few other means. One way that comes to mind is by selecting upon the PathName() of the FileStream field. Assume that the FileStream enabled field is ReportData, and the table in which it resides is TblReports. The following t-sql syntax will yield an UNC to the location:

select top 1 ReportData.PathName(0)
from dbo.datReport

I believe you can also get at the path by other means through enterprise manager, but I forget how to at the moment.

Answer 2

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.