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.
|
1 2 3 4 |
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.
| D | Full Backup |
| I | Differential Backup |
| L | Log Backup |
| F | File veya Filegroup Backup |
| G | Differential File Backup |
| P | Partial Backup |
| Q | Differential partial Backup |
