Location of all last X backups – all DBs

Check the location of backups for the last 7 days for all databases, or narrow down to a single one
Useful to see where the backups are going, and if there are any failed backups during the last week

/*
DESCRIPTION List location of last X days backup for all databases.
CONFIGURATION Set @days for the number of days to search for backups
Set @dbname for the specific database; leave NULL for all
 
Compatibility list:
MSSQL2012 MSSQL2005 MSSQL2008 MSSQL2000  
*/
 

DECLARE @days INT
DECLARE @dbname sysname

SET @days = 7			-- Number of days
SET @dbname = NULL		-- Specify database like 'database1' . If Null it'll do all

SELECT
 serverproperty('servername')
, serverproperty('productversion')
, serverproperty('productlevel')
, bs.database_name
, datediff(ss,bs.backup_start_date, bs.backup_finish_date) 'Duration_Sec'
, bs.backup_finish_date
, CASE bs.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'TLog'
END AS backup_type
, ceiling(bs.backup_size/10214/1021) 'BK_Size_MB'
--, ceiling(bs.compressed_backup_size/10214/1021) 'BK_Size_MB' -- Only on 2008 and up
, bmf.physical_device_name
--, bmf.device_type
--, bs.user_name
 
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE 1=1
and bmf.family_sequence_number = '1'
and (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - @days)
-- and msdb..backupset.type = 'D'
and bs.database_name like coalesce(@dbname,'%')
ORDER BY
bs.database_name,
bs.backup_finish_date
 

Leave a Reply

Your email address will not be published. Required fields are marked *