database_files not matching master_files in database in standby mode

database_files not matching master_files in database in standby mode by Louis Davidson

Active

Type:	Bug
ID:	124491
Opened:	5/23/2006 10:07:01 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When database is in standby mode after a restore, sys.database_files (and sys.sysfiles) does not match sys.master_files. Database files contains the file names from the database being restored. Master_files has the right names.

Details (expand)

English

SQL Server 2005 Service Pack 1 (32)

SQL Engine

Windows XP Professional

English

See attached for full batch.

Restored databse with:
--restore with standby
RESTORE DATABASE testFileLoc_test
FROM DISK = 'c:\temp\1EE13753-30DA-4570-AB2B-E36B5EA0B561.bak'
WITH MOVE 'testFileLoc' to 'c:\temp\testFileLoc_test.mdf',
MOVE 'testFileLoc_log' to 'c:\temp\testFileLoc_test.ldf',
STANDBY = 'c:\temp\test.log'
go
In the new database queried:

SELECT physical_name AS [sys.database_files.physical_name]
FROM sys.database_files

SELECT physical_name AS [sys.database_files.physical_name]
FROM sys.master_files
WHERE database_id = db_id()

The sys.master_files location was correct, the database files has the filenames and paths of the original file.

These should match, right? Once applying a log and no longer in standby they do match (full repro attached)

0 attachments

Hi Louis, A standby database is a readonly database and this is consistent with other readonly cases. Consider readonly attach for instance.
use master
go
sp_configure show, 1
go
reconfigure with override
go
sp_configure 'xp_cmdshell', 1
go
sp_configure 'Agent XPs', 1
go
reconfigure with override
go
sp_configure show, 0
go
--
-- Create a database
--
create database foo
on (name='foodata', filename='c:\foo.mdf', size=5)
log on (name='foolog', filename='c:\foo.ldf', size=5)
go
use foo
go
IF (DB_NAME() = N'master')
RAISERROR('A problem was encountered accessing user DB. Terminating.', 20, 127) WITH LOG
go
use master
go
--
-- Close the files by setting it offline
--
ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
--
-- Copy the files off and attach them as a readonly database
--
exec master.dbo.xp_cmdshell 'copy c:\foo.?df c:\bar.?df'
go
exec master.dbo.xp_cmdshell 'attrib +r c:\bar.?df'
go
alter database foo set online
go
create database bar on
(filename='c:\bar.mdf'), (filename='c:\bar.ldf')
for attach
go
--
-- Will show foo paths
--
select * from bar.sys.database_files
go
--
-- Will show bar paths
--
select * from master.sys.master_files where database_id = db_id ('bar')
go
drop database foo, bar
go
exec master.dbo.xp_cmdshell 'attrib -r c:\bar.?df'
go
exec master.dbo.xp_cmdshell 'del /Q c:\bar.?df'
go

Thank you.