(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!