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

Question: How to Get Details of All files Associated with Database from MDF?

Answer: Just the other day I received an email from a blog reader pointing to my earlier blog post SQL SERVER – Database Attach Failure – Msg 2571 – User ‘guest’ Does Not Have Permission to Run DBCC Checkprimaryfile. The question was about the DBCC command used in the blog post – dbcc checkprimaryfile.

His original question was very simple – Is it safe to run dbcc checkprimaryfile.

The answer is absolutely it is very safe to run the DBCC command and in my career of almost two decades, I have not seen any issue created by this command. With that said, you should always try out anything suggested on this blog post on the development server before deploying it on the production.

After exchanging a few emails with him, I realized that not many people know how powerful the command dbcc checkprimaryfile can be.

The command takes dbcc checkprimaryfile two parameters. The first parameter is the Physical File Name and the second parameter is an integer value between 0 and 3.

Let us see what each of the value represents:

0 – returns value 1 if the file a primary database file
1 – returns name, size, max size, status and path of all files associated with the database
2 – returns the database name, version, and collation
3 – returns name, status, and path of all files associated with the database

Let us run our DBCC command with all the four options and see what kind of output it gives us.

DBCC CHECKPRIMARYFILE('D:\data\SQLAuthority.mdf',0);
DBCC CHECKPRIMARYFILE('D:\data\SQLAuthority.mdf',1);
DBCC CHECKPRIMARYFILE('D:\data\SQLAuthority.mdf',2);
DBCC CHECKPRIMARYFILE('D:\data\SQLAuthority.mdf',3);

Let us see the output of the above queries.

As you can see with the help of dbcc checkprimaryfile you can Get Details of All files Associated with Database from MDF file. I hope you find this helpful. If you have any interesting tips to share with everyone, I request all of you to share the same.

Reference: Pinal Dave (https://blog.sqlauthority.com)