Filestream file metadata

Question

question

rfazley asked May 15, '20

Filestream file metadata

We're migrating our internally stored varbinary data in sql server 2016 to filestream. Part of the project is to scan the files for viruses once the files have been extracted to the local file system using a bespoke c# console app (using sophos savdi).

If we do find any vulnerabilities with the files we'd like some "metadata" recorded against it in a csv file so that we can advise our clients.

My question is, is there a way I can derive the table, column and primary key that the physical filestream file (i.e. 00000022-000001a8-0007) refers to?

filestream guid

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-05-15T10:46:58Z

Kev Riley answered May 15, '20

Not easily....

The filename is actually the Log Sequence Number from the database transaction log at the time that the file was created, so I guess you could iterate around the tables that have filestream columns

select o.name
from sys.columns c
join sys.objects o on o.object_id = c.object_id
where is_filestream = 1

and then use DBCC PAGE to get the LSNs and then match them up.

You'll need the info that Paul Randal describes here: https://www.sqlskills.com/blogs/paul/filestream-directory-structure/

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rfazley commented · May 15, 2020 at 11:23 AM

Thank you. That's what I thought and the solution I have in place - I'm iterating through records and logging the pk and the corresponding filestream lsn into a table that I can use to compare against the malware scans. You would think Microsoft would've made it easier to get this kind of basic information.

0 ·

question