Enumerate file structure within detached MDF database file using SMO

calendar Jan 7, 2015  · Updated: November 1, 2025 · 2 min read  ·
Share on: linkedin bluesky copy

While researching how connect to a detached MDF and read the internal file structure of the internal files, including LDF files, I kept seeing suggestions for using the undocumented DBCC command DBCC checkprimaryfile. Initially, my code looked like this:

 1# SQL Server is required to read the file contents
 2$servername = "sqlserver\instance"
 3$mdf = "S:\DATA\mydb.mdf"
 4[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
 5$server = New-Object Microsoft.SqlServer.Management.Smo.Server $servername
 6
 7# Use the undocumented DBCC checkprimaryfile function to automatically determine
 8# where the files are located. If you moved the files, please see below.
 9$sql = "DBCC checkprimaryfile(N'$mdf',3)"
10$dataset = $server.ConnectionContext.ExecuteWithResults($sql)
11$dataset.Tables.Filename.Trim()

After some exploration, I discovered that this task can actually be accomplished purely in SMO using EnumDetachedDatabaseFiles and EnumDetachedLogFiles.

 1$servername = "sqlserver\instance"
 2$mdf = "S:\DATA\mydb.mdf"
 3
 4[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
 5$filestructure = New-Object System.Collections.Specialized.StringCollection
 6$server = New-Object Microsoft.SqlServer.Management.Smo.Server $servername
 7
 8# Here you can automatically determine the database name, or set it manually
 9$dbname = ($server.DetachedDatabaseInfo($mdf) | Where { $_.Property -eq "Database name" }).Value
10 
11foreach    ($file in $server.EnumDetachedDatabaseFiles($mdf)) {
12	$null = $filestructure.add($file)
13}
14
15foreach ($file in $server.EnumDetachedLogFiles($mdf)) {
16	$null = $filestructure.add($file)
17}

Note (2025): The approach above still works, but loading SMO with LoadWithPartialName is deprecated. Prefer using the SqlServer PowerShell module, which ships and loads the SMO assemblies for you and supports PowerShell 7+ on Windows, Linux, and macOS.

1# Install once if needed
2Install-Module SqlServer -Scope CurrentUser
3
4# Then in your session
5Import-Module SqlServer
6
7# SMO types are now available, e.g.
8$server = New-Object Microsoft.SqlServer.Management.Smo.Server 'sqlserver\instance'

DBCC CHECKPRIMARYFILE remains undocumented; using the SMO methods EnumDetachedDatabaseFiles, EnumDetachedLogFiles, and DetachedDatabaseInfo avoids relying on an undocumented statement. For module reference, see: https://learn.microsoft.com/powershell/module/sqlserver/