SQL Server

Where are the backup files? – SQL Server

June 8, 2016

In the previous post we explored backupset table in msdb, which holds data about backups, backup times etc. So now that we have good backups, where are the backup files? We can query backupmediafamily table for this information. physical_device_name column holds the full path (if saved on disk else the media name) to backup file.

SELECT * FROM msdb.dbo.backupmediafamily

We can also join backupmediafamily to backupset on media_set_id column to get information about relevant backup files.

SELECT physical_device_name, bs.backup_start_date, bs.backup_finish_date FROM msdb.dbo.backupmediafamily bf
INNER JOIN msdb.dbo.backupset bs ON bf.media_set_id = bs.media_set_id
WHERE 1=1
AND bf.media_set_id = 18518

You Might Also Like

No Comments

Leave a Reply