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
David And Data 