Location of last X backups – Single DB

Check the location of backups for the last 7 days for a single database. Useful to see where the backups are going, and if there is any fail backup. Very similar to
http://www.cookingsql.com/2014/10/backups-location-of-all-last-x-backups-all-dbs/

 /*
DESCRIPTION    List location of  all backup for a single database.
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

                          Set @dbname for the database

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000

 */ 
declare @dbname varchar(500)
declare @LastXdays nvarchar(2)
set @dbname = 'master'
set @LastXdays = '30'
  
SELECT
    serverproperty('servername')
    ,s.database_name
    ,s.is_copy_only
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
 
--  ,cast(s.compressed_backup_size/1000000 as varchar(24))+' '+'MB' as bkSize  -- only on SQL2008
    ,CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(6))+' '+'Seconds' TimeTaken
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,CASE s.[type]
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
        END as BackupType
    -- ,s.recovery_model    --- does not work 2000
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
ORDER BY  s.backup_finish_date desc

Leave a Reply

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