Connect With MyTechMantra.com













"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."


Identify Database Properties Using DBCC CHECKPRIMARYFILE Undocumented DBCC Command


Sept 30, 2014


Introduction

DBCC CHECKPRIMARYFILE is one among the many undocumented SQL Server DBCC Command. Using this DBCC Command a Database Administrator can Identify Database Properties such as Database Name, FileName, Version and Collation etc.

DBCC CHECKPRIMARYFILE command reads the metadata from the primary database data file (.mdf) file and returns very useful information such as Database Name, Logical Names, Physical File Names etc without attaching the database to the SQL Server. As this being an undocumented command it is not recommended to use this in production environment.

DBCC CHECKPRIMARYFILE Syntax

DBCC CHECKPRIMARYFILE ({'FileName'} [, opt = {0|1|2|3}])

FileName is nothing but the actual path of the SQL Server Database Primary Data File .mdf file.

Opt = 0 - Verifies whether the file is a SQL Server Database Primary Data file (.mdf).
Opt = 1 - Returns Database Name, Size, Max Size, Growth, Status and Path of all files associated to the database.
Opt = 2 - Returns Database Name, Version and Collation information.
Opt = 3 - Returns Name, Status and Path of all files associated to the database.

Let us go through in detail the output returned by each of the above mentioned options of DBCC CHECKPRIMARYFILE Undocumented DBCC Command.

Using DBCC CHECKPRIMARYFILE Command to verify whether the file is a SQL Server Database Primary Data File

For demonstrate purposes a database by the name MyTechMantra is created and the data and log file of the database is stored in D:\Database folder.

If the file is an .mdf file then the below command will return the value as “1”. If it is not an .mdf file of any database then it will return the value as “0” .

/* Returns Value as "1" as MyTechMantra.mdf is a Primary Data File */

DBCC CHECKPRIMARYFILE ('D:\Database\MyTechMantra.mdf', 0)
GO

/* Returns Value as "0" as MyTechMantra.ldf is a Transaction Log File */

DBCC CHECKPRIMARYFILE ('D:\Database\MyTechMantra.ldf', 0)
GO


Find whether the database file is a SQL Server Database Primary Data File or not

Using DBCC CHECKPRIMARYFILE Command Find Database Name, Size, Max Size, Growth, Status and Path of all files associated to the database

/* Returns Database Name, Size, Max Size, Growth, Status and Path of all files associated to the database */

DBCC CHECKPRIMARYFILE ('D:\Database\MyTechMantra.mdf', 1)
GO


Using DBCC CHECKPRIMARYFILE Command Find Database Name, Size, Max Size, Growth, Status and Path of all files associated to the database

Using DBCC CHECKPRIMARYFILE Command Find Database Name, Version and Collation information

/* Returns Database Name, Version and Collation information */

DBCC CHECKPRIMARYFILE ('D:\Database\MyTechMantra.mdf', 2)
GO


Using DBCC CHECKPRIMARYFILE Command Find Database Name, Version and Collation information

Using DBCC CHECKPRIMARYFILE Command Find Name, Status and Path of all files associated to the database

/* Returns Name, Status and Path of all files associated to the database */

DBCC CHECKPRIMARYFILE ('D:\Database\MyTechMantra.mdf', 3)
GO


Using DBCC CHECKPRIMARYFILE Command Returns Name, Status and Path of all files associated to the database

Conclusion

DBCC CHECKPRIMARYFILE is a very useful command which helps you identify all the database files names and path. If you know the location of .mdf file then using this DBCC command one can identify all the relevant .ndf and .ldf files of the database. This DBCC command can be of great help especially during scenarios when you have detached multiple databases at the same time and finally you want to attach the database back to your SQL Server Instance.

Reference: Undocumented commands


Did this article help you? Then Do Like Us on Facebook!


Share this Article



LEARN MORE...
 
Winners
White Papers
Product Reviews
Trending News
All Articles
Free Tools
 
Follow Us...

Related Articles…