I have two queries with me. One pulls the below columns from the msdb.dbo.backupfile by backupset_uuid by joining backup_set_id with backupset table.
Other pulls the same columns from the sys.filegroups and sys.databases based on the database name.
With my current setup, for a backupset and the database, I get the same primary and log files as output. I want to know if there is any possibility where for a database the db files will be different from the database files that i get for the backupset. Steps to create such database and backups will be helping. Kindly note the database is in restoring mode.
Also I noted that the state of file in the first case(backupset based) in Online mode as filtered but in second case(database name) the state of files is Restoring. Is it dependent on when the entires are created?
SELECT col1, col2, col3, col4, col5, col6, FROM msdb.dbo.backupfile bf JOIN msdb.dbo.backupset bs ON bf.backup_set_id = bs.backup_set_id WHERE bf.state = 0 AND bs.backup_set_uuid = '.....'
SELECT md.col1, md.col2, md.col3, md.col4, md.col5, f.col6 FROM sys.filegroups f RIGHT OUTER JOIN (SELECT m.col1, m.col2, m.col3, m.col4, m.col5 FROM sys.master_files m JOIN sys.databases d ON m.database_id = d.database_id WHERE d.name = '...') AS md ON md.data_space_id = f.data_space_id;