Just recently we switched from our old fashioned, yet reliable MS SQL Server backups to EMCs DataDomain backups. In doing this our 2 TB backups went from being 4-8 hours to being greater than 13 hours (had to kill them twice when they were greater than 19 hours).  One of the things that we needed to do to show EMC the issue was to show the times before and after the switch took place.  Thank god for MSDB.  We built this script to find the data quickly and efficiently:

SELECT
	, 'FULL' AS [Type_Of_Backup]
	, CASE WHEN [SBS].[name] = 'DDBMMSSQL' THEN 'DataDomain' ELSE 'SQLBackups' END AS [Backup_Location]
	, [SBS].database_name
	, [SBS].[backup_start_date]
	, [SBS].[backup_finish_date]
	, RIGHT( '00' + CAST(DATEDIFF(HOUR,[SBS].[backup_start_date], [SBS].[backup_finish_date]) AS VARCHAR(3)), 2)
		+ ':' +
		CASE WHEN RIGHT( '00' + CAST(DATEDIFF(MINUTE,[SBS].[backup_start_date], [SBS].[backup_finish_date])
						- (DATEDIFF(Hour,[SBS].[backup_start_date], [SBS].[backup_finish_date]) * 60) AS VARCHAR(3)), 2) < 0 THEN '00' ELSE
						RIGHT( '00' + CAST(DATEDIFF(MINUTE,[SBS].[backup_start_date], [SBS].[backup_finish_date])
						- (DATEDIFF(Hour,[SBS].[backup_start_date], [SBS].[backup_finish_date]) * 60) AS VARCHAR(3)), 2) END
	AS RunTime
	, DATEDIFF(MINUTE,[SBS].[backup_start_date], [SBS].[backup_finish_date]) AS RunTimeMinutes
FROM msdb.dbo.backupset AS [SBS]
WHERE
	[SBS].database_name = '<;database to measure>;'
	AND [SBS].type='D' --This means the full backups.
	AND([SBS].user_name='<;Service Account>;'OR [SBS].user_name='<;SQL Agent Account>;')

Forgive my programming skills since I pride myself as being an accidental DBA and come from the hardware side and not the development side.  I am still learning and will always make room for improvement.

Advertisements

Leave a Reply

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