The Curious Case of… how to find FILESTREAM info from an MDF

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

A couple of weeks ago a question came in to the MCM email distribution list where someone wanted to know how to find FILESTREAM info from an MDF. They were working with a client who needed to attach a database and all they had was the MDF – no backup, and no access to the database attached anywhere else. The attach was failing because the database had a FILESTREAM filegroup defined, and so the CREATE DATABASE … FOR ATTACH statement needed to specify the FILESTREAM filegroup name and a new location for it.

Given that there was no SQL Server access to the database or a backup, the only way to find the information was through examining the MDF directly.

Here are the steps I recommended:

• Download and install the freeware hex editor HxD from here.
• Open the MDF file in HxD.
• Hit Search, change the data type to hex values and search for the string ‘6C 00 64 00 66 00’, which is the Unicode byte-reversed hex for ‘ldf’.
• Hit F3 until it can’t find any more. Now you’re positioned in the list of file for the database.
• Just below that will be pathnames of other files, so scroll down until you come to one that doesn’t have ldf, mdf, or ndf at the end of the pathname.
• This is the pathname for the FILESTREAM data container, and above that is the filegroup name.

As an example, the image below shows a portion of the MDF for a test database I created:

C:\SQLskills\Documents is the pathname and FSWaitsDocuments is the logical name.

Bottom line: you never know when a little hex-editor spelunking will come in handy!