Day: February 14, 2020

Here is a script to list SQL server backup details for the last two weeks  or 14 days.

SELECT
     bs.database_name                                               AS DatabaseName
     ,CASE
        WHEN bs.database_name IN ('model','master','tempdb','msdb')
        THEN 1
        ELSE 0
     END                                                            AS IsSystemDatabase
    ,bs.backup_start_date                                           AS StartTime
    ,bs.backup_finish_date                                          AS FinishTime
    ,bs.expiration_date												AS ExpiryTime
    ,DATEDIFF(MI, bs.backup_start_date, bs.backup_finish_date)      AS DurationMin
    ,DATEDIFF(SS, bs.backup_start_date, bs.backup_finish_date)      AS DurationSec
    ,CASE bs.type
        WHEN 'D' THEN 'Full Backup'
        WHEN 'I' THEN 'Differential Database'
        WHEN 'L' THEN 'Log'
        WHEN 'F' THEN 'File/Filegroup'
        WHEN 'G' THEN 'Differential File'
        WHEN 'P' THEN 'Partial' 
        WHEN 'Q' THEN 'Differential partial'
     END                                                            AS BackupType
    ,CAST(ROUND(((bs.backup_size/1024)/1024),2) AS decimal(10,2))   AS BackupSizeMb
    ,CAST(
        ROUND(((bs.compressed_backup_size/1024)/1024),2) 
                                                AS decimal(18,2))   AS CompressedSizeMb
    , ' '+ISNULL(
      CASE WHEN has_bulk_logged_data = 1 THEN '| Bulk Logged Data' END
    + CASE WHEN is_snapshot = 1 THEN '| Snapshot' END
    + CASE WHEN is_readonly = 1 THEN '| Read-Only' END
    + CASE WHEN is_single_user = 1 THEN '| Single User' END
    + CASE WHEN has_backup_checksums = 1 THEN '| Backup Checksums' END
    + CASE WHEN is_damaged = 1 THEN '| Damaged' END
    + CASE WHEN begins_log_chain = 1 THEN '| Begins Log Chain' END
    + CASE WHEN has_incomplete_metadata = 1 THEN '| Incomplete Metadata' END
    + CASE WHEN is_force_offline = 1 THEN '| Force Offline' END
    + CASE WHEN is_copy_only = 1 THEN '| Copy Only' END
    ,'')                                            AS BackupOptions    
    ,bmf.physical_device_name                       AS BackupFile
    ,bs.user_name                                   AS ExecUser
FROM msdb.dbo.backupmediafamily AS bmf
    INNER JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id
WHERE DATEDIFF(dd, bs.backup_finish_date, GETDATE()) <= 14
ORDER by bs.backup_start_date  DESC;