DBCC checkprimaryfile - An Useful Undocumented DBCC Command | SansSQL

Sunday, December 2, 2012

DBCC checkprimaryfile - An Useful Undocumented DBCC Command

Consider you are given with a data file and asked to tell the details about the associated database and the files without attaching the database. What would be your answer?
I would have said "No, it is not possible" if I was asked this few days ago. But now, I say "Yes, It is possible to some extent" using the undocumented DBCC command "DBCC checkprimaryfile"

Before using this DBCC command, you have to note that
  1. This is not recommend to use in Production environment
  2. The database file should be detached from the SQL Server
  3. This works with MDF files
DBCC checkprimaryfile ({'FileName'} [,opt={0|1|2|3}])

FileName is the full path for the primary database file.
opt=0 - checks if the file a primary database file.
opt=1 - returns name, size, maxsize, status and path of all files associated with the database.
opt=2 - returns the database name, version and collation.
opt=3 - returns name, status and path of all files associated with the database.

DBCC checkprimaryfile ('C:\Users\SANDESH\Desktop\MSDBData.mdf', 0)
DBCC checkprimaryfile ('C:\Users\SANDESH\Desktop\MSDBData.mdf', 1)

DBCC checkprimaryfile ('C:\Users\SANDESH\Desktop\MSDBData.mdf', 2)

DBCC checkprimaryfile ('C:\Users\SANDESH\Desktop\MSDBData.mdf', 3)