How to Get Details of All Files Associated with Database from MDF? – Interview Question of the Week #275

Question: Can an MDF reveal the other files associated with its database? The historical DBCC CHECKPRIMARYFILE command can inspect metadata in a primary data file. For an attached database, use the documented catalog views instead.

A reader asked about this command after reading my database-attach permission error article. Their first question was whether it was safe. I’d used it diagnostically for years, but that experience is not a guarantee about every file or version.

It is an undocumented command. Keep the original files and test on a disposable copy. It is not a repair operation, an integrity check or proof that a database can be attached successfully.

-- Historical diagnostic command. Use a disposable copy, not the only MDF.
DBCC CHECKPRIMARYFILE (N'D:\data\SQLAuthority.mdf', 0);
DBCC CHECKPRIMARYFILE (N'D:\data\SQLAuthority.mdf', 1);
DBCC CHECKPRIMARYFILE (N'D:\data\SQLAuthority.mdf', 2);
DBCC CHECKPRIMARYFILE (N'D:\data\SQLAuthority.mdf', 3);

The following are separate native crops from the original SQLAuthority.mdf example. Each crop removes unused space and the old added annotations, keeping the actual result pixels.

The paths describe metadata stored in this MDF. They don’t prove that the other files still exist at those locations. The internal database version number is also not the same as the product’s familiar release name.

When the database is already attached

Run the supported catalog query in that database:

SELECT file_id, name, type_desc, physical_name,
       size * 8.0 / 1024 AS size_MB
FROM sys.database_files
ORDER BY file_id;

sys.master_files provides the instance-wide file inventory for attached databases. If you have a backup instead of an MDF, RESTORE FILELISTONLY is the documented way to inspect its file list.

Microsoft: sys.database_files describes the supported catalog path. Keep the legacy diagnostic in its proper role rather than turning a useful tip into an absolute safety claim.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.