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:
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 checkpriamryfile 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}