Mark S. Rasmussen improve.dk
May 19
2011

Inspired by this question on StackOverflow, I’ve made a quick script to demonstrate how this might be done using OrcaMDF.

In this example I’m looping all .mdf files in my local SQL Server data directory. Each one is loaded using OrcaMDF, the boot page is fetched and finally the database name is printed:

using System;
using System.IO;
using OrcaMDF.Core.Engine;

namespace OrcaMDF.Adhoc
{
    class Program
    {
        static void Main()
        {
			foreach (string mdfPath in Directory.GetFiles(@"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA"))
			{
				if (!mdfPath.ToLower().EndsWith(".mdf"))
					continue;

				using (var file = new MdfFile(mdfPath))
				{
					var bootPage = file.GetBootPage();
					Console.WriteLine(bootPage.DatabaseName);
				}
			}
        }
    }
}

And the following is the output we get:

Which, coincidentally, matches up to the databases I’ve got attached to my local SQL Server. At this point we could match this list up to the one we’d get from sys.databases and see which files didn’t have a matching database, and thus weed out the non-attached mdf files from our data directory.

Mark S. Rasmussen
I'm the CTO at iPaper where I cuddle with databases, mold code and maintain the overall technical & team responsibility. I'm an avid speaker at user groups & conferences. I love life, motorcycles, photography and all things technical. Say hi on Twitter, write me an email or look me up on LinkedIn.