Checking Which Database is Stored in a Detached MDF File

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.