About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Sunday, May 20, 2012

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




No comments:

Post a Comment