sys.database_files and sys.master_files show different physical_name values

Question

I received the following alert:

The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x0000000012c000 in file 'C:\VeeamFLR\UK->SB->SQL11_4406bb77\Volume3\DATA\MyDB10_STAGE.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and...

The following script shows sys.database_files and sys.master_files have different physical_name values:

declare @dbname varchar(30)

set @dbname = (select name from sys.master_files where physical_name like 'C:\VeeamFLR\%.mdf')

select name as 'MasterFiles', physical_name as 'FullPath' from sys.master_files where physical_name like 'C:\VeeamFLR\%.%df'order by physical_name desc
exec('USE '+ @dbName + '; select name as ''DatabaseFiles'', physical_name as ''FullPath'' from sys.database_files')

Results:

MasterFiles FullPath

------------------------ ------------------------------------------------------------------------

MyDB10_STAGE C:\VeeamFLR\UK-SB-SQL11_4406bb77\Volume3\DATA\MyDB10_STAGE.mdf

MyDB10_STAGE_log C:\VeeamFLR\UK-SB-SQL11_4406bb77\Volume2\Logs\MyDB10_STAGE_log.ldf

(2 row(s) affected

DatabaseFiles FullPath

------------------------ ------------------------------------------------------------------------

MyDB10_STAGE E:\DATA\MyDB10_STAGE.mdf

MyDB10_STAGE_log D:\Logs\MyDB10_STAGE_log.ldf

(2 row(s) affected)

The sys.database_files value is correct. (Sys.master_files refers to a previous Veeam restore mount point. Not sure why). I've tried a detach and re-attach with the correct path specified:

-- detach:
ALTER DATABASE MyDB10_STAGE SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'MyDB10_STAGE'
GO

-- No need to move physical files as actually already in correct location

USE [master]
GO
CREATE DATABASE MyDB10_STAGE ON
( FILENAME = N'E:\DATA\MyDB10_STAGE.mdf' ),
( FILENAME = N'D:\LOGS\MyDB10_STAGE_log.ldf' )
FOR ATTACH
GO

--Put database in multi user mode again:

ALTER DATABASE MyDB10_STAGE SET MULTI_USER
GO

But this has not updated physical_name in sys.master_files. Can anyone recommend how to update physical_name in sys.master_files to the correct value please?

Answer 1

I couldn't find anything about this in the documentation for sys.database_files or sys.master_files, or even sys.sysfiles or sys.sysaltfiles, but there will likely be several state changes that won't be truly reflected across all file-related catalog views until the SQL Server service has been restarted. If the changes are isolated to a single database, it might be worth trying to set the database OFFLINE and ONLINE again, but I haven't confirmed that will work.