How to tie filestream GUID value to actual file name?

chrisrdba 361

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?

Olaf Helper 40,901 Reputation points

2022-05-12T05:49:46.757+00:00

I don't understand what you are trying to achieve here, may can you explain it with samples, please?
CathyJi-MSFT 21,096 Reputation points Microsoft Vendor

2022-05-12T08:04:00.947+00:00

Hi @chrisrdba ,

Please check if below thread could help you.

SQL Filestream - tying up rows with File Stream files
chrisrdba 361 Reputation points

2022-05-12T14:17:32.467+00:00

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!
chrisrdba 361 Reputation points

2022-05-12T14:19:46.863+00:00

Thanks, but as mentioned I'm aware of column.pathname , but that doesn't truly show what I need.
Tom Phillips 17,716 Reputation points

2022-05-12T19:19:00.65+00:00

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.
chrisrdba 361 Reputation points

2022-05-12T20:06:04.393+00:00

I need the real filename for a one-time archival process.
Tom Phillips 17,716 Reputation points

2022-05-12T20:28:58.713+00:00

There is no reason to interact with the physical files on the FILESTREAM system. You simply backup the data using the database, as you would with any other table.
chrisrdba 361 Reputation points

2022-05-12T22:27:55.507+00:00

We're trying to do a one-time archival and shut the app down. However, we do want to save some small amounts of meta data and the associated file, and of course tie them together.

From my user: "Desired end state as far as I’m concerned is a metadata CSV file with a column for the file name, and then a folder of the files"

I thought about an SSRS report that included the filestream column like this article but we have several file types and the file type needs to be specified to make this magice work.
Tom Phillips 17,716 Reputation points

2022-05-13T20:36:19.297+00:00

That is not how FILESTREAM works. The physical files used by FILESTREAM is completely invisible. This is all managed from within the table.

You simply backup the database and restore the database.

I believe the end user is asking for the actual filename, not the FILESTREAM autogenerated physical filename.
chrisrdba 361 Reputation points

2022-05-13T21:58:30.993+00:00

My end user is a power user -- he brought me this when he was unable to write a query to get what he was after.

He would be fine with either the actual filename or the autogenerate physical filename if he could tie it back to the meta data.
Erland Sommarskog 101.4K Reputation points MVP

2022-05-14T08:36:23.443+00:00

"power user" = may be too smart for his own good.

A filename only makes sense if there is a filename stored in the database. And that would not be the filename in the Filestream area. The filename in the FILESTREAM filegroup is private to SQL Server and not usable outside SQL Server. And not really if you copy the files out of the area elsewhere.

Accepted answer

Erland Sommarskog 101.4K Reputation points MVP

2022-05-13T20:26:05.027+00:00

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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

How to tie filestream GUID value to actual file name?

0 additional answers