How to tie filestream GUID value to actual file name?

Question

question

chrisrdba asked May 11, '22 ErlandSommarskog commented May 14, '22

How to tie filestream GUID value to actual file name?

Greetings. I've seen a few people ask this question on google w no real solution.

We're using filestream, and I want to run a query in the DB that will allow me to tie the result of that query to the actual name of the filestream file.

This link shows the column filestream_value_name which is precisely what I'm after, but only shows stuff waiting to be backed up, while running in DAC. I need something that can tie every row of data in my table to the actual file name as seen in Explorer (for example).

I'm also aware of column.pathname , but that doesn't truly show what I need.

I'm also aware that manually manipulating these files is not supported, and could lead to corruption. I have no intention of doing that.

Any ideas?

sql-server-general

· 11

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 · May 12 at 05:49 AM

I don't understand what you are trying to achieve here, may can you explain it with samples, please?

0 ·

chrisrdba OlafHelper-2800 · May 12 at 02:17 PM

See attached. From the first link above, the query would suggest that the files are named "0x5048....." and "0xFFD....." while the OS clearly has no files w those names.

How do I write a query that will give me the real names the OS sees?

Thanks!

0 ·

fsfiles.jpg (80.7 KiB)

TomPhillips-1744 chrisrdba · May 12 at 07:19 PM

The column "file" is the actual binary contents of the file, not a filename.

column.pathname , but that doesn't truly show what I need.

Why? This provides the full path to the actual file.

What are you trying to accomplish by getting the filename? The physical file is completely managed by SQL Server and is really meaningless.

0 ·

Show more comments

Cathyji-msft · May 12 at 08:04 AM

Hi @chrisrdba,

Please check if below thread could help you.

SQL Filestream - tying up rows with File Stream files

0 ·

chrisrdba Cathyji-msft · May 12 at 02:19 PM

Thanks, but as mentioned I'm aware of column.pathname , but that doesn't truly show what I need.

0 ·

Answer 1 · 2022-05-13T20:26:05.027Z

ErlandSommarskog answered May 13, '22

It may be better to play it safe and write a program that trawls the Filestream table and extracts the files with the OpenSqlFilestream API and writes the file and the metadata. Yes, this means that you need to find a programmer that can write such a program. Then again, this is not 200 lines of code.

This also gives you a consistent view of the contents. If you read the raw file area directly, you will get old versions and deleted files that have not been garbage-collected yet.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question