Most companies have a backup strategy, maintenance procedures. In my article “SQL Server Maintenance“, you can find details about maintenance on sql server.
Therefore, the directory where the backups are located is usually known. However, in some cases, you can take backup except regular backup. In such a case, you can find the all backup’s paths of a database and backup times with the help of the following script.
select FAM.physical_device_name,BS.* from dbo.backupset BS
inner join dbo.backupmediafamily FAM ON FAM.media_set_id=BS.media_set_id
where database_name='your_db_name' and type='D' and is_copy_only=1
order by backup_finish_date desc
The letter D in the section “type = ‘D'” means full backup. You can use the following letters to query other backup types.
The condition “is_copy_only = 1” in the script also means that the backup is copy_only. You can perform this query for all backups by removing this condition.
|F||File veya Filegroup Backup|
|G||Differential File Backup|
|Q||Differential partial Backup|