Checking detached data file details

DBCC CheckPrimaryfile


DBCC checkprimaryfile ( 'C:\Program Files\Microsoft SQL Server\MSSQL$RAJ\data\master.mdf' , 1 )


Result shown below.



If you have a data file which belongs to the Primary file group of the database, then DBCC CheckPrimaryfile command can provide details about the Database it belonged to. Provides the details such as Original Data file and log file location,other data files location details , physical names of the files in the database and size details. This info can be very useful when you perform migrations where databases have to be moved to different servers. After detaching the database, in case, if you want to figure out the location and other files attached to the original database then this command will help for sure.

Second parameter takes 0,1,2,3 as valid inputs. Each option provides a useful information.Not documented.So please do the research about the command yourself.

Works on 2000/2005. Note: Doesn't work if the Data file originally belonged to SQL 2005 and the command is issued from SQL 2000 Server. Other way around works. ie. SQL 2000 Data file can be checked from SQL 2005 server.