Why there is a difference between sp_helpfile vs sys.master_files?

Question

Hi while restoring database using restore command in sql server to specific drive, after executing sp_helpfile its showing incorrect file path, however sys.master_files showing right file path ? why sp_helpfile showing inaccurate path? can somebody please help me to understand this?

Answer 1

Too long to comment.

I suspect you aren't connected to the correct database when you run sp_helpfile. If you query sys.master_files it will return the same thing regardless of what database you are connected to. However, sp_helpfile used the default connection if you don't specify a file name. Thus, if you are connected to [master] it will return results for the files of this database.

Answer 2

sp_helpfile output comes from the database, sys.master_files is the file physical location. When you restore a database before recovery, sp_helpfile get the file location from the backup which shows the file location at the time the backup was made. After restore and recovery, the file location in database will be changed to the physical location as the one in sys.master_file

Answer 3

Using exec sp_helptext N'sp_helpfile' brings up the definition for sp_helpfile.

This shows that it is using sys.sysfiles, which is an old 2000-era system view, which is now for compatibility only. Just one more reason not to use the horrible sp_help procedures.

Comparing the definition for sys.sysfiles and sys.database_files shows tha both use the internal system table or view sys.sysprufiles. The only primary difference is that sysfiles uses the filter:

WHERE filetype IN (0, 1) -- x_eft_SQLData, x_eft_SQLLog (bwkcmpt types)
  AND filestate NOT IN (1, 2, 3) -- x_efs_Dummy, x_efs_Dropped, x_efs_DroppedReusePending

whereas database_files uses only

WHERE filestate NOT IN (1, 2)   -- x_efs_Dummy, x_efs_Dropped

On the other hand, you are comparing with sys.master_files, which is a global view, uses a different system table sys.sysbrickfiles. It has the following filters:

WHERE f.dbid < 0x7fff -- consistent with sys.databases
  AND f.pruid = 0
  AND f.filestate NOT IN (1, 2) -- x_efs_Dummy, x_efs_Dropped

Neither of these are in the list of base tables, so either they are undocumented, or they are views to other base tables.

Exactly what the difference between them is is unclear, but master_files is global across the server, so it's possible taht its source is not being updated correctly, or is a little behind on updates.