One of the questions that might come to your mind while getting started with FILESTREAM is the way FILESTREAM directories and files are named. Within the FILESTREAM Data Container, SQL Server creates a root folder per FILESTREAM enabled table. If the table is partitioned, a folder will be created for every partition. Within the root folder of the table (or partition), a sub folder will be created for every FILESTREAM column in the table.

You might find the way these folders named very confusing. FILESTREAM folders show up as GUID values such as “5c3769cb-b53a-4da3-a5a8-d5e8ff31fb45”. There was no known way of relating a folder name with a table, until Paul Randal came up with an interesting blog post which shows a query that can be used to relate the directories to their respective tables and columns. You can find Paul’s blog post here.

The query that Paul posted reads the information from some internal system tables which is not available when you connect to SQL Server normally. To access these internal tables, you need to connect to SQL Server using a special method known as Dedicated Administrator Connection (DAC). To establish a DAC to your SQL Server instance, you can do the following.

1. From SSMS File menu, select New > Database Engine Query
2. In the connection dialog, prefix your server name with “ADMIN:”. If your server name is DEVSERVER, enter ADMIN:DEVSERVER and then enter the username and password (or go with windows authentication) and click on connect.

Notes:

1. It looks like you cannot make a Dedicated Admin Connection using the Connect button on the object explorer window.
2. You can have only one DAC opened at a time. You cannot have a second query window opened with a DAC to the same sever.
3. For additional references on DAC, read this MSDN article.