Don't always believe your system procs - they may lie !
It's sometimes interesting how a simple thing can catch you out!
I have a set of routines which handle the process of restoring copies of databases on other servers, it also supports full blown log shipping but has various other functionality.
Generally all my servers have identical file locations for SQL Server, e.g F: for data L: for logs etc.
For one reason or another I have a server which has only one data drive so the logs are now on drive F: too.
My restore database proc correctly generates move statements to deal with exactly this situation, but it uses sp_helpfile to gather the location of the physical database files, it's been happily working in SQL 2000, 2005 and 2008 ( with suitable changes to system tables )
What I find in SQL 2008 is that when the database is in standby sys.database_files and hence the output for sp_helpfile returns the location of the files when the database was backed up, it's source file locations not it's current location. If the database is not in standby the current file locations are returned.
The solution is to use sys.master_files not the local databases files.
I did some searching and discovered an entry on Connect by Loius Davidson http://connect.microsoft.com/SQLServer/feedback/details/124491/database-files-not-matching-master-files-in-database-in-standby-mode
It seems Microsoft say this is by design, now this may be so but what I say is that the behaviour of sp_helpfile and the contents of sys.database_files is inconsistent. I'd always expect it to be the same, it was this way in SQL 2000.
Note that BOL makes no mention that sp_helpfile will not return correct information for read-only / standby database
I've only stumbled across this because I have what I call a non standard server.
( My searches didn't seem to find any other mentions of this, however if I am the only DBA who didn't know then I apologise !! )