Mapping database backup with the backup location:-
select BS.database_name,BS.backup_finish_date BackupDateTime, BS.backup_size/1024/1024 SizeInMB, BMF.physical_device_name
from dbo.backupset BS, dbo.backupmediafamily BMF
where BS.media_set_id=BMF.media_set_id and BS.type=’D’
–BS.type= D means full backup
order by BS.database_name, BS.backup_finish_date
We can use the above script for multiple purpose, like:-
1) It can be used to check backup details foe full backup, incremental backup, transaction backup, etc. Just use the appropriate backup type. Just replace BS.type with –
D = Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
2) It can be helpful, when we are not able to apply t-log backup during recovery, because some one took additional t-log backup at some other location.
3) If we do not maintain database growth then we can get the backup size of the database in history and can calculate the approximate database growth.
4) We can get lots of information regarding database backups by querying dbo.backupmediafamily and dbo.backupset tables in msdb database.