vwBackupHistory

Here is a view that JOINs the pertinent system tables in msdb to present a concise view of backup history.

This view has three dependencies which are easy to install:

        udfFormatInteger

        udfGetFilenameOnly

        udfGetPathOnly

USE Admin

GO

IF object_id('vwBackupHistory') Is Not Null

      DROP VIEW dbo.vwBackupHistory

GO

CREATE VIEW dbo.vwBackupHistory

AS

/*    DATE        AUTHOR            REMARKS

      5/20/12           PPaiva            Initial creation.

     

      -- Last 10 backups per DB per Type per Path  

      SELECT *

      FROM vwBackupHistory

      WHERE Num <= 10

        AND DB Not In ('master', 'model', 'msdb')

      ORDER BY 1, 2, 3, 4, 5, 6

      SELECT *

      FROM vwBackupHistory

      ORDER BY DateStarted desc

      -- DBs backed up

      SELECT Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName, Count(*) Qty

      FROM vwBackupHistory

      GROUP BY Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName

      ORDER BY Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName

      SELECT *

      FROM vwBackupHistory

      WHERE BackupType <> 'Log'

      ORDER BY DateFinished desc,   DB

      SELECT Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName, Count(*) Qty

      FROM vwBackupHistory

      GROUP BY Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName

      ORDER BY Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName

*/

SELECT  b.machine_name Machine,

            b.server_name SqlInstance,

            b.database_name DB, 

            CASE b.type 

                  WHEN 'D' THEN 'Database' 

                  WHEN 'L' THEN 'Log' 

                  WHEN 'I' THEN 'Differential Database'

                  WHEN 'F' THEN 'File or FileGroup'

                  WHEN 'G' THEN 'Differential File'

                  WHEN 'P' THEN 'Partial'

                  WHEN 'Q' THEN 'Differential Partial'

                  WHEN Null THEN 'Null'

                  ELSE 'Need to define in view'

                        END AS BackupType,

            b.recovery_model RecoveryModel,

            ROW_NUMBER() OVER (PARTITION BY b.Machine_name, b.server_name, b.database_name, b.Type, b.Recovery_Model ORDER BY backup_start_date desc) Num,

            b.backup_start_date DateStarted, 

                  DATEDIFF(second, b.backup_start_date, b.backup_finish_date) BackupSecs,

                  DATEDIFF(minute, b.backup_start_date, b.backup_finish_date) BackupMins,

                  dbo.udfFormatInteger((b.backup_size /1000000.)) SizeMBFmt,

                  dbo.udfGetFilenameOnly(f.physical_device_name) Filename,

                  dbo.udfGetPathOnly(f.physical_device_name) Path,

            bms.is_compressed IsCompressed,             -- add this column for 2008 and higher

            b.compressed_backup_size CompressedSizeBytes,      -- add this column for 2008 and higher

            b.backup_size SizeBytes, 

            b.is_copy_only IsCopy,

            b.is_damaged IsDamaged,

            b.description,

            b.expiration_date ExpDate,

            f.physical_device_name PathFilename,  

            b.name AS BackupSetName,

            f.logical_device_name LogicalDeviceName,

            fg.name FileGroup,

            bms.software_name SoftwareName,

            b.backup_finish_date DateFinished

FROM msdb.dbo.backupmediafamily  f

JOIN msdb.dbo.backupset b

      ON f.media_set_id = b.media_set_id 

JOIN msdb.dbo.backupmediaset bms

      ON bms.media_set_id = b.media_set_id

JOIN msdb.dbo.backupfilegroup fg

      ON fg.backup_set_id = b.backup_set_id

GO