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!

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.