I was asked to retrieve a list of all database backups within the last week. Unfortunately, I forgot about the backupset table within msdb. However, Tim Ford posted a great blog article on the backupset table and included a few examples. After a few modifications, here is what I used:
SELECT CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN ‘D’ THEN ‘Database’
WHEN ‘L’ THEN ‘Log’
END AS backup_type,
(CONVERT(VARCHAR, CONVERT(DECIMAL(18, 2), msdb.dbo.backupset.backup_size/1024/1024), 1)) AS backup_size_in_mb,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() – 7)
ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date
