Compare last two backups by type, across all dates or on two specific dates

compare-last-backups-on-dates.sql

USE msdb
GO

WITH cte AS
(
	SELECT
		[database_name], [type] AS backup_type
	,	backup_size, compressed_backup_size
	,	backup_start_date, backup_finish_date
	,	CAST(backup_finish_date - backup_start_date AS time(0)) elapsed
	,	ROW_NUMBER() OVER (PARTITION BY [database_name], [type], DATEADD(dd, DATEDIFF(dd, 0, backup_start_date), 0) ORDER BY backup_start_date DESC) as rn
	FROM	backupset
)
SELECT
	T1.[database_name], T1.backup_type
,	T1.elapsed AS elapsed_new
,	T2.elapsed AS elapsed_old
,	CASE
	WHEN T1.elapsed < T2.elapsed
	THEN 'faster'
	WHEN T1.elapsed > T2.elapsed
	THEN 'slower'
	WHEN T1.elapsed = T2.elapsed
	THEN 'same'
	END AS elapsed_diff
,	CASE
	WHEN T1.elapsed < T2.elapsed
	THEN CONVERT(time(0), DATEADD(ms, DATEDIFF(ss, T1.elapsed, T2.elapsed) * 1000, 0), 114)
	WHEN T1.elapsed >= T2.elapsed
	THEN CONVERT(time(0), DATEADD(ms, DATEDIFF(ss, T2.elapsed, T1.elapsed) * 1000, 0), 114)
	END AS elapsed_diff_time
,	T1.compressed_backup_size AS compressed_backup_size_new
,	T2.compressed_backup_size AS compressed_backup_size_old
,	T1.compressed_backup_size - T2.compressed_backup_size AS compressed_backup_sizee_diff
,	CONVERT(decimal(17, 2), (T1.compressed_backup_size - T2.compressed_backup_size) / 1024. / 1024.) AS compressed_backup_size_diff_mb
,	CONVERT(decimal(17, 2), (T1.compressed_backup_size - T2.compressed_backup_size) / 1024. / 1024. / 1024.) AS compressed_backup_size_diff_gb
,	T1.backup_size AS backup_size_new
,	T2.backup_size AS backup_size_old
,	T1.backup_size - T2.backup_size AS backup_size_diff
,	CONVERT(decimal(17, 2), (T1.backup_size - T2.backup_size) / 1024. / 1024.) AS backup_size_diff_mb
,	CONVERT(decimal(17, 2), (T1.backup_size - T2.backup_size) / 1024. / 1024. / 1024.) AS backup_size_diff_gb
FROM	cte AS T1
JOIN	cte AS T2
  ON	T1.[database_name] = T2.[database_name]
 AND	T1.backup_type = T2.backup_type
WHERE	T1.rn = 1 AND T2.rn = 1
  AND	DATEADD(dd, DATEDIFF(dd, 0, t1.backup_start_date), 0) = '20210124'
  AND	DATEADD(dd, DATEDIFF(dd, 0, t2.backup_start_date), 0) = '20210123'
  --AND	T1.backup_type = 'D'

compare-last-backups.sql

WITH cte AS
(
	SELECT
		[database_name], [type] AS backup_type
	,	backup_size, compressed_backup_size
	,	backup_start_date, backup_finish_date
	,	CAST(backup_finish_date - backup_start_date AS time(0)) elapsed
	,	ROW_NUMBER() OVER (PARTITION BY [database_name], [type] ORDER BY backup_start_date DESC) as rn
	FROM	backupset
)
SELECT
	T1.[database_name], T1.backup_type
,	T1.elapsed AS elapsed_new
,	T2.elapsed AS elapsed_old
,	CASE
	WHEN T1.elapsed < T2.elapsed
	THEN 'faster'
	WHEN T1.elapsed > T2.elapsed
	THEN 'slower'
	WHEN T1.elapsed = T2.elapsed
	THEN 'same'
	END AS elapsed_diff
,	CASE
	WHEN T1.elapsed < T2.elapsed
	THEN CONVERT(time(0), DATEADD(ms, DATEDIFF(ss, T1.elapsed, T2.elapsed) * 1000, 0), 114)
	WHEN T1.elapsed >= T2.elapsed
	THEN CONVERT(time(0), DATEADD(ms, DATEDIFF(ss, T2.elapsed, T1.elapsed) * 1000, 0), 114)
	END AS elapsed_diff_time
,	T1.compressed_backup_size AS compressed_backup_size_new
,	T2.compressed_backup_size AS compressed_backup_size_old
,	T1.compressed_backup_size - T2.compressed_backup_size AS compressed_backup_sizee_diff
,	CONVERT(decimal(17, 2), (T1.compressed_backup_size - T2.compressed_backup_size) / 1024. / 1024.) AS compressed_backup_size_diff_mb
,	CONVERT(decimal(17, 2), (T1.compressed_backup_size - T2.compressed_backup_size) / 1024. / 1024. / 1024.) AS compressed_backup_size_diff_gb
,	T1.backup_size AS backup_size_new
,	T2.backup_size AS backup_size_old
,	T1.backup_size - T2.backup_size AS backup_size_diff
,	CONVERT(decimal(17, 2), (T1.backup_size - T2.backup_size) / 1024. / 1024.) AS backup_size_diff_mb
,	CONVERT(decimal(17, 2), (T1.backup_size - T2.backup_size) / 1024. / 1024. / 1024.) AS backup_size_diff_gb
FROM	cte AS T1
JOIN	cte AS T2
  ON	T1.[database_name] = T2.[database_name]
 AND	T1.backup_type = T2.backup_type
WHERE	T1.rn = 1 AND T2.rn = 2
--  AND	T1.backup_type = 'D'