SQL Server: Visualize Your Backups For Insights – By Month Day, Time etc.

Backups are a very important part of a DBA’s job. Understanding when they have happened and when they have not happened is even more important. Quantifying things quickly can mean the difference between good and bad decisions when you are in a bind.

Note: If you are looking to do something similar with Oracle, please checkout my post on Data Visualization of DateTime with Oracle

Mundane view of the backups:

--Source of this subquery: https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/
--
SELECT
	CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
	msdb.dbo.backupset.database_name,
	msdb.dbo.backupset.backup_start_date,
	cast(msdb.dbo.backupset.backup_start_date as time) [time],
	RIGHT ('00' +
		lTRIM(STR(DATEPART(dd, msdb.dbo.backupset.backup_start_date))), 2) AS BackupMonthDay,
	CASE msdb..backupset.type
	WHEN 'D' THEN 'Database'
	WHEN 'L' THEN 'Log'
	END AS backup_type,
	msdb.dbo.backupset.backup_size
FROM msdb.dbo.backupmediafamily
	INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
	CONVERT(DATE, msdb.dbo.backupset.backup_start_date) >= (GETDATE() - 16)
	AND msdb.dbo.backupset.database_name LIKE '%TFS%'
order by
	Server,
	database_name,
	backup_type;

The output is:

BackupsMundaneView.jpg

There is a lot of data in there but information is hard to come by because of how the data is visualized.

In the series of queries that follow let us see how we can inject meaning into the above data by simply displaying it differently.

Visualization: Backup count in the last [x] days

First, let us see how many backups were done each day of the month in the last 16 days

Add/change the conditions as you see fit!


IF object_id('TEMPDB.DBO.#TempCountByMonthDays') IS NOT NULL
	DROP TABLE #TempCountByMonthDays;
GO

WITH Pivoted
AS
(
	SELECT *
	FROM
	(
		--Source of this subquery: https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/
		--
		SELECT
			CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
			msdb.dbo.backupset.database_name,
			msdb.dbo.backupset.backup_start_date,
			cast(msdb.dbo.backupset.backup_start_date as time) [time],
			RIGHT ('00' +
				lTRIM(STR(DATEPART(dd, msdb.dbo.backupset.backup_start_date))), 2) AS BackupMonthDay,
			CASE msdb..backupset.type
			WHEN 'D' THEN 'Database'
			WHEN 'L' THEN 'Log'
			END AS backup_type,
			msdb.dbo.backupset.backup_size/1024/1024 AS backup_size_mb
		FROM msdb.dbo.backupmediafamily
			INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
		WHERE
			CONVERT(DATE, msdb.dbo.backupset.backup_start_date) >= (GETDATE() - 16)
			AND msdb.dbo.backupset.database_name LIKE '%TFS%'
	) a
	PIVOT (
	   COUNT(BackupMonthDay)
	   FOR BackupMonthDay IN ([01],[02],[03],[04],[05],[06], [07],
					[08],[09],[10],[11],[12],[13],
					[14],[15],[16],[17],[18],[19],
					[20],[21],[22],[23],[24],[25],
					[26],[27],[28],[29],[30],[31])
		) bkups
)
SELECT *
INTO #TempCountByMonthDays
FROM Pivoted;

--Count of backups each hour in every 24 hour period
--Ordered overall by database name, backup type and backup start date DESC
SELECT
	Server,
	database_name,
	backup_type,
	COUNT(1) AS TotalBackups,
	SUM([01]) AS [01], SUM([02]) AS [02], SUM([03]) AS [03], SUM([04]) AS [04], SUM([05]) AS [05],
	SUM([06]) AS [06], SUM([07]) AS [07], SUM([08]) AS [08], SUM([09]) AS [09], SUM([10]) AS [10], SUM([11]) AS [11],
	SUM([12]) AS [12], SUM([13]) AS [13], SUM([14]) AS [14], SUM([15]) AS [15], SUM([16]) AS [16], SUM([17]) AS [17],
	SUM([18]) AS [18], SUM([19]) AS [19], SUM([20]) AS [20], SUM([21]) AS [21], SUM([22]) AS [22], SUM([23]) AS [23],
	SUM([24]) AS [24], SUM([25]) AS [25], SUM([26]) AS [26], SUM([27]) AS [27], SUM([28]) AS [28], SUM([29]) AS [29],
	SUM([30]) AS [30], SUM([31]) AS [31]
FROM #TempCountByMonthDays
GROUP BY
	Server,
	database_name,
	backup_type
HAVING
	1=1
	AND database_name NOT IN ('master','model','msdb')
	--AND backup_type = 'DATABASE'
	--AND database_name like 'AUS%'
ORDER BY
	Server,
	database_name,
	backup_type;

DROP TABLE #TempCountByMonthDays;

…and here is the output made pretty with Excel

  • Replaced all zeroes with empty string
  • Color scale is by count value. Green means more, red means less backups

Columns 1 through 16 is actually the “Day Of the Month”. 17th through 31’st is not shown.

CountByMonthDays

The numbers 1 through 16 reflect the calendar days of the month from the 1st through the 16th of this month. The numbers inside are the backup counts (for Database or Log backups) for that particular day

Inferences:

I can immediately, infer a few things

  • For the 3 databases, log and database backups are being done
  • On most days about 15 log backups are done for each database
  • On some days 1 or 2 full database backups are done
  • There were no full backups done between the 3rd and 12th!
  • Starting on the 13th, once a day full backups were done daily

If you did not think that it was pretty enlightening, you must be lying. 😉

Visualization: Backup size (MB) in the last [x] days

Let us see the total size backups for each day of the month in the last 16 days

The below output is for the first 16 days of this calendar month (today is 16th, last 15 days)


IF object_id('TEMPDB.DBO.#TempSizeByMonthDays') IS NOT NULL
	DROP TABLE #TempSizeByMonthDays;
GO

WITH Pivoted
AS
(
	SELECT *
	FROM
	(
		--Source of this subquery: https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/
		--
		SELECT
			CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
			msdb.dbo.backupset.database_name,
			msdb.dbo.backupset.backup_start_date,
			cast(msdb.dbo.backupset.backup_start_date as time) [time],
			RIGHT ('00' +
				lTRIM(STR(DATEPART(dd, msdb.dbo.backupset.backup_start_date))), 2) AS BackupMonthDay,
			CASE msdb..backupset.type
			WHEN 'D' THEN 'Database'
			WHEN 'L' THEN 'Log'
			END AS backup_type,
			msdb.dbo.backupset.backup_size/1024/1024 AS backup_size_mb,
			msdb.dbo.backupset.backup_size/1024/1024 AS backup_size_mb2
		FROM msdb.dbo.backupmediafamily
			INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
		WHERE
			CONVERT(DATE, msdb.dbo.backupset.backup_start_date) >= (GETDATE() - 16)
			AND msdb.dbo.backupset.database_name LIKE '%TFS%'
	) a
	PIVOT (
	   SUM(backup_size_mb)
	   FOR BackupMonthDay IN ([01],[02],[03],[04],[05],[06], [07],
					[08],[09],[10],[11],[12],[13],
					[14],[15],[16],[17],[18],[19],
					[20],[21],[22],[23],[24],[25],
					[26],[27],[28],[29],[30],[31])
		) bkups
)
SELECT *
INTO #TempSizeByMonthDays
FROM Pivoted;

--Count of backups each hour in every 24 hour period
--Ordered overall by database name, backup type and backup start date DESC
SELECT
	Server,
	database_name,
	backup_type,
	COUNT(1) AS TotalBackupCount,
	SUM(backup_size_mb2) AS TotalBackupSize,
	SUM([01]) AS [01], SUM([02]) AS [02], SUM([03]) AS [03], SUM([04]) AS [04], SUM([05]) AS [05],
	SUM([06]) AS [06], SUM([07]) AS [07], SUM([08]) AS [08], SUM([09]) AS [09], SUM([10]) AS [10], SUM([11]) AS [11],
	SUM([12]) AS [12], SUM([13]) AS [13], SUM([14]) AS [14], SUM([15]) AS [15], SUM([16]) AS [16], SUM([17]) AS [17],
	SUM([18]) AS [18], SUM([19]) AS [19], SUM([20]) AS [20], SUM([21]) AS [21], SUM([22]) AS [22], SUM([23]) AS [23],
	SUM([24]) AS [24], SUM([25]) AS [25], SUM([26]) AS [26], SUM([27]) AS [27], SUM([28]) AS [28], SUM([29]) AS [29],
	SUM([30]) AS [30], SUM([31]) AS [31]
FROM #TempSizeByMonthDays
GROUP BY
	Server,
	database_name,
	backup_type
HAVING
	1=1
	AND database_name NOT IN ('master','model','msdb')
	--AND backup_type = 'DATABASE'
	--AND database_name like 'AUS%'
ORDER BY
	Server,
	database_name,
	backup_type;

DROP TABLE #TempSizeByMonthDays;

Again, the output is below (formatted with Excel). Columns 1 through 16 is actually the “Day Of the Month”. 17th through 31’st is not shown.

SizeMBByMonthDays

Inferences:

  • The approximate size of the databases is known from the Database backup size
  • Full database backups were not done between the 3rd and 13th
  • Backups were possibly done twice on the 2nd (an anomaly in sizes)
  • TFS_Warehouse is the smallest and TFS_DefaultCollection, the largest
  • Database sizes remained almost constant in the last few days

Visualization: Backup count by hour for the last [x] days

Below, we see the backup counts by each calendar date (DESC) and the count of backups that happened in each hour for the backup type. You have to think for a second before you look at the data but it is pretty amazing what you can see.

IF object_id('TEMPDB.DBO.#TempCountByHour') IS NOT NULL
	DROP TABLE #TempCountByHour;
GO

WITH Pivoted
AS
(
	SELECT *
	FROM
	(
		--Source of this subquery: https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/
		--
		SELECT
			CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
			msdb.dbo.backupset.database_name,
			msdb.dbo.backupset.backup_start_date,
			cast(msdb.dbo.backupset.backup_start_date as time) [time],
			RIGHT ('00' +
				lTRIM(STR(DATEPART(HH, msdb.dbo.backupset.backup_start_date))), 2) AS BackupHour,
			CASE msdb..backupset.type
			WHEN 'D' THEN 'Database'
			WHEN 'L' THEN 'Log'
			END AS backup_type,
			msdb.dbo.backupset.backup_size/1024/1024 AS backup_size_mb
		FROM msdb.dbo.backupmediafamily
			INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
		WHERE CONVERT(DATE, msdb.dbo.backupset.backup_start_date) >= (GETDATE() - 16)
			AND msdb.dbo.backupset.database_name LIKE '%TFS%'
	) a
	PIVOT (
	   COUNT(BackupHour)
	   FOR BackupHour IN ([00],[01],[02],[03],[04],[05],[06], [07],
					[08],[09],[10],[11],[12],[13],
					[14],[15],[16],[17],[18],[19],
					[20],[21],[22],[23])
		) bkups
)
SELECT *
INTO #TempCountByHour
FROM Pivoted;

--Count of backups each hour in every 24 hour period
--Ordered overall by database name, backup type and backup start date DESC
SELECT
	Server,
	database_name,
	backup_type,
	CONVERT(DATE, backup_start_date) AS backup_date,
	SUM(backup_size_mb) AS Total_Size_MB,
	COUNT(1) AS TotalBackups,
	SUM([00]) AS [00], SUM([01]) AS [01], SUM([02]) AS [02], SUM([03]) AS [03], SUM([04]) AS [04], SUM([05]) AS [05],
	SUM([06]) AS [06], SUM([07]) AS [07], SUM([08]) AS [08], SUM([09]) AS [09], SUM([10]) AS [10], SUM([11]) AS [11],
	SUM([12]) AS [12], SUM([13]) AS [13], SUM([14]) AS [14], SUM([15]) AS [15], SUM([16]) AS [16], SUM([17]) AS [17],
	SUM([18]) AS [18], SUM([19]) AS [19], SUM([20]) AS [20], SUM([21]) AS [21], SUM([22]) AS [22], SUM([23]) AS [23]
FROM #TempCountByHour
GROUP BY
	Server,
	database_name,
	backup_type,
	CONVERT(DATE, backup_start_date)
HAVING
	1=1
	AND database_name NOT IN ('master','model','msdb')
	AND CONVERT(DATE, backup_start_date) >= (GETDATE() - 14)
	--AND backup_type = 'DATABASE'
	--AND database_name like 'AUS%'
ORDER BY
	Server,
	database_name,
	backup_type,
	CONVERT(DATE, backup_start_date) DESC

DROP TABLE #TempCountByHour;

The output is below

Columns 0 through 23 is actually the “Hour Of the Day” as in 0000 hours to 2400 hours.

CountByHour
Inferences:

There are a few things we can see immediately:

  • Total backup size for the days we limited the query to (16 days)
  • Log backups seem to be happening almost every hour
  • Log backs for TFS_Configuration on 2/13/2018 had issues between 0200 hours and 0800 hours!
  • Full backups seem to be happening on the 11th hour 🙂 every day (1100 hours)
  • No backups happen between 1800 and 2200 hours
  • …plus a lot more that I cannot see but you might!

Visualization: Backup size by hour for the last [x] days

Finally, let get the size of the backups by hour for each day. It is the same as the previous one but we get the size instead of the count so that we know how much data we wrote out in each hour for the backups

This is only for the last 16 days and only for the log backups. You can change the highlighted conditions


IF object_id('TEMPDB.DBO.#TempSizeByHour') IS NOT NULL
	DROP TABLE #TempSizeByHour;
GO

WITH Pivoted
AS
(
	SELECT *
	FROM
	(
		--Source of this subquery: https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/
		--
		SELECT
			CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
			msdb.dbo.backupset.database_name,
			msdb.dbo.backupset.backup_start_date,
			RIGHT ('00' +
				lTRIM(STR(DATEPART(HH, msdb.dbo.backupset.backup_start_date))), 2) AS BackupHour,
			CASE msdb..backupset.type
			WHEN 'D' THEN 'Database'
			WHEN 'L' THEN 'Log'
			END AS backup_type,
			msdb.dbo.backupset.backup_size/1024/1024 AS backup_size_mb,
			msdb.dbo.backupset.backup_size/1024/1024 AS backup_size_mb2
		FROM msdb.dbo.backupmediafamily
			INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
		WHERE CONVERT(DATE, msdb.dbo.backupset.backup_start_date) >= (GETDATE() - 16)
			AND msdb.dbo.backupset.database_name LIKE '%TFS%'
			AND msdb..backupset.type = 'L'
	) a
	PIVOT (
	   SUM(backup_size_mb)
	   FOR BackupHour IN ([00],[01],[02],[03],[04],[05],[06], [07],
					[08],[09],[10],[11],[12],[13],
					[14],[15],[16],[17],[18],[19],
					[20],[21],[22],[23])
		) bkups
)
SELECT *
INTO #TempSizeByHour
FROM Pivoted;

SELECT
	Server,
	database_name,
	backup_type,
	CONVERT(DATE, backup_start_date) AS backup_date,
	SUM(backup_size_mb2) AS TotalBackupSize,
	COUNT(1) AS TotalBackupCount,
	SUM([00]) AS [00], SUM([01]) AS [01], SUM([02]) AS [02], SUM([03]) AS [03], SUM([04]) AS [04], SUM([05]) AS [05],
	SUM([06]) AS [06], SUM([07]) AS [07], SUM([08]) AS [08], SUM([09]) AS [09], SUM([10]) AS [10], SUM([11]) AS [11],
	SUM([12]) AS [12], SUM([13]) AS [13], SUM([14]) AS [14], SUM([15]) AS [15], SUM([16]) AS [16], SUM([17]) AS [17],
	SUM([18]) AS [18], SUM([19]) AS [19], SUM([20]) AS [20], SUM([21]) AS [21], SUM([22]) AS [22], SUM([23]) AS [23]
FROM #TempSizeByHour
GROUP BY
	Server,
	database_name,
	backup_type,
	CONVERT(DATE, backup_start_date)
HAVING
	1=1
	AND database_name NOT IN ('master','model','msdb')
	AND CONVERT(DATE, backup_start_date) >= (GETDATE() - 14)
	--AND backup_type = 'DATABASE'
	--AND database_name like 'AUS%'
ORDER BY
	Server,
	database_name,
	backup_type,
	CONVERT(DATE, backup_start_date) DESC

DROP TABLE #TempSizeByHour

The output is below (columns renamed a bit to make room):

Columns 0 through 23 is actually the “Hour Of the Day” as in 0000 hours to 2400 hours.

SizeByHour

Inferences:

Besides the inferences already made, we can see that more activity happened in the database where you see red color and less activity where it is green.

Conclusion:

Pivoting feature of SQL Server/Oracle is a great feature that helps you look at the data differently to gain insights that you otherwise will not be able to get easily without BI tools.

You could not only do this type of visualization for backups but also for other things!

Advertisements

3 thoughts on “SQL Server: Visualize Your Backups For Insights – By Month Day, Time etc.

  1. HI,

    This is a great way of visualising your backups; nice touch and thank you for sharing!
    I think you need a distinct in your query for Backup count in the last [x] days i.e. SELECT DISTINCT
    CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server….etc

    I was getting duplicates. Also maybe worth adding inclusion of incremental backups in the case stmt

  2. Hi Ness. Thank you.

    At that subquery (that you noted) level, there could be duplicates for a database if multiple backups were done, especially of the same type (database/log). If that is the case the backup startdate would be different. Did you see a case where all the column values are the same but there are duplicates? In other words, did adding the DISTINCT reduce the number of rows for you? Adding a DISTINCT should not have made any difference in the results. When aggregated up with the “GROUP BY” in the final SQL, it should be grouped and there should be no duplicates (on Database+BackupType).

    Good idea for incremental backups. I will add it in when I get a chance.

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s