SPROC: Backup History Report

A while back, I wrote a script to show backup throughput over time. It was intended to diagnose deteriorating disk IO performance for the backup destination. I figured it could be useful for other things as well, so I combined it with my backup history report into a single stored procedure. I hope you find this as useful as I do. Please let me know if you have any questions on it, or suggestions for improvements.

Thanks for reading,
-David.

USE [SQLAdmin];
GO

IF (SELECT OBJECT_ID('dbo.ShowCurrentBackupHistory')) IS NULL
BEGIN
    EXEC
('CREATE PROCEDURE dbo.ShowCurrentBackupHistory
            AS
            PRINT ''STUB VERSION - Replace with actual procedure.'';
            '
)
END
GO

ALTER PROCEDURE dbo.ShowCurrentBackupHistory
/*******************************************************************************
AUTHOR:
  David M Maxwell
  
DATE:
  Nov 6 2013

DESCRIPTION:
  
  Shows the last @days of backup history for all databases.

  Shows name, current recovery model, backup type (full, diff or log), finish time,
  duration in seconds, backup size in MB, throughput in MB per second, user name
  that took the backup, and the backup file destination.

DEPENDENCIES:
  None.
  
TEST EXEC:

EXEC dbo.ShowCurrentBackupHistory
   @days = 30 -- Last 30 days of history
GO

TO DO LIST:
  1.

NOTES:  
  Please send any comments or suggestions to dmmaxwell@gmail.com.

CHANGE HISTORY:
Date - Author - Change
Nov 6 2013 - David M Maxwell - Initial version. Pending feedback.

*******************************************************************************/
  
@days int = 30
AS

SET NOCOUNT ON
;

/* Convert the @days to a datetime to keep things simple. */
DECLARE @dt datetime
SELECT
@dt = dateadd(dd,-@days,getdate())

SELECT
  
DBName = bs.database_name
,CurrentRecModel = db.recovery_model_desc /* IF NULL, then DB no longer exists. */
,BackupType =
  
CASE bs.type
    WHEN
'D' THEN 'Full'
    
WHEN 'I' THEN 'Diff'
    
WHEN 'L' THEN 'TLog'
    
ELSE 'Unknown'
  
END
,DateCompleted = bs.backup_finish_date
,Duration = DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
,
DataSizeMB = CAST(ROUND(bs.backup_size / 1048576.0,2) AS numeric(10,2))
,
[MB/sec] = CAST(ROUND((bs.backup_size / 1048576.0) /
      
CASE
        
WHEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 0
        
THEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
        
ELSE 1
      
END,2) AS numeric(10,2))
,
UserName = bs.[user_name]
,BackupFile = bmf.physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
  
ON bs.media_set_id = bmf.media_set_id
LEFT OUTER JOIN master.sys.databases db
  
ON bs.database_name = db.name
WHERE bs.backup_finish_date > @dt
ORDER BY
  
bs.backup_finish_date DESC;
GO
Advertisements

Please Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s