9/12/2012

List of recent database backups

Usually we need to find list of recent database backups. When the backup was taken and where it was saved. Below script will get a 5 recent backups
DECLARE @DBNAME VARCHAR(128)

SET @DBNAME = 'DB_Name'

SELECT TOP 5 
 A.database_name
 ,B.physical_device_name
 ,A.media_set_id
 ,A.backup_size
 ,A.backup_start_date
 ,A.backup_finish_date
 
FROM msdb.dbo.backupset A
 INNER JOIN msdb.dbo.backupmediafamily B
ON 
 A.media_set_id = B.media_set_id
WHERE 
A.Database_Name= @DBNAME

ORDER BY A.backup_finish_date DESC