question

rfazley avatar image
rfazley asked

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?

filestreamguid
10 |1200

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

1 Answer

·
Kev Riley avatar image
Kev Riley answered

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 comment
10 |1200

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

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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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