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?

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 = '.....'

For database:

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;


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.