content top
OMG, my DB files are gone!

OMG, my DB files are gone!

Recently I encountered an issue on one of our client's DB which was part of an Always On Availability Group.

When I used the GUI (SSMS) to find the DB's file location I saw that they are on the G drive (while they were actually on H drive):

 SSMS files locations

To be sure, I Checked the sys.master_files DMV – surprise!, Files are on H drive…

Apparently, the SSMS is using a query that gets the data from sys.database_files.

Querying the DMV sys.database_files – showed us that the locations for the mdf files are not where they should be.

When I ran the following select, to compare between the two DMVs, I saw that for the same files – we're getting two different locations:

select A.file_id, A.physical_name as database_files_physical_name, B.physical_name as master_files_physical_name

from sys.database_files  A

join  sys.master_files B on A.file_guid = B.file_guid

Join database_files and master_files

What is the reason for this difference?

When going over books online, I saw that the physical name in the sys.master_files is from the Operating-system file name.

While in the view sys.database_files, physical name indicates the "file location of the primary replica database."

If you need the correct file location of a readable secondary database, you need to query sys.master_files.

Is this a BUG in SSMS? Did Microsoft intended for this behavior?

תגובה אחת על “OMG, my DB files are gone!”

  1. מאת אלכס פרידמן:

    מומלץ בחום לשים את הקבצים באותם נתיבים בכל הרפליקות. אחרת זה מתכון לצרות בכל שינוי אח"כ, כמו הוספת קובץ.

כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

ten + 12 =