Enumerate file structure within detached MDF database file using SMO

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:

# SQL Server is required to read the file contents
$servername = "sqlserver\instance"
$mdf = "S:\DATA\mydb.mdf"
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $servername

# Use the undocumented DBCC checkpriamryfile function to automatically determine
# where the files are located. If you moved the files, please see below.
$sql = "DBCC checkprimaryfile(N'$mdf',3)"
$dataset = $server.ConnectionContext.ExecuteWithResults($sql)
$dataset.Tables.Filename.Trim()

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

$servername = "sqlserver\instance"
$mdf = "S:\DATA\mydb.mdf"

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$filestructure = New-Object System.Collections.Specialized.StringCollection
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $servername

# Here you can automatically determine the database name, or set it manually
$dbname = ($server.DetachedDatabaseInfo($mdf) | Where { $_.Property -eq "Database name" }).Value
 
foreach    ($file in $server.EnumDetachedDatabaseFiles($mdf)) {
	$null = $filestructure.add($file)
}

foreach ($file in $server.EnumDetachedLogFiles($mdf)) {
	$null = $filestructure.add($file)
}

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server

Leave a Reply