SQL SERVER: Backups info (Can be visualized in OUTLOOK)

GetBackupInformation.sql

/*
	Tim C: This query can be used as a normal query, or it can also be used to import into an OUTLOOK calendar so 
	you can visualize your backups. Similar to https://dbatools.io/timeline/
	
	RECOMMENDATIONS:
		1) You should import into a CUSTOM blank excel calendar
		2) You should not import TLOG backups, as that will utterly bloat the calendar
		
	******************************************************************************************
	TO CREATE A NEW CALENDAR:
		- Switch to the calendar view
		- Click the Folder menu
		- Click New Calendar on the menu bar
	******************************************************************************************
	TO IMPORT TO EXCEL:
		- Save results as CSV, ensure the headers are saved into the CSV
		- In Outlook click the File menu
		- Click on open and Export
		- Click Import/Export
		- Select "Import from another program or file"
		- Click Next
		- Select "Comma seperated values"
		- Click Next
		- Browse to and select the CSV saved from these query results
			- Optional: If you have imported results previously, you should select "Replace duplicates with items imported"
		- Select the custom destination calendar folder previously created. (Highly advise not to import to an existing calendar)
		- Click the checkbox in front of the import file name
		- The important fields should auto match up.
			- Subject, start date, start time, end date, end time, description
			- If some of these fields did not auto map, you will have to manually map
		- Click OK
		- Click Finish
	******************************************************************************************
	TO DELETE ALL ITEMS OUT OF A CALENDAR:
		OPTION 1) 
			- Delete the calendar and recreate
		OPTION 2) 
			- In Calendar, click View.
			- In the Current View group, click Change View, and then click List.
			- Click in the item list, and then press Ctrl+A. All items in the list should now be selected.
			- Press Delete.
*/


SELECT [server_name] = fn.server  
	,[subject] = CONCAT(fnInfo.server, ' - ', bs.[database_name], ' - ', bs.type)
	,[backup_week_day] = DATENAME(weekday, bs.backup_start_date) 
	,[start date] = FORMAT(bs.[backup_start_date], 'yyyy-MM-dd HH:mm:ss')
	,[start time] = FORMAT(bs.[backup_start_date], 'yyyy-MM-dd HH:mm:ss')
	,[end date] = FORMAT(bs.[backup_finish_date], 'yyyy-MM-dd HH:mm:ss')
	-- this column is for excel. the calendar does not support very short intervals, so add 15 if less than 15
	,[end time] = FORMAT(CASE 
		WHEN DATEDIFF(MINUTE,bs.backup_start_date, bs.backup_finish_date) < 15 
			THEN DATEADD(MINUTE, 15, bs.backup_start_date) 
		ELSE bs.backup_start_date 
	END, 'yyyy-MM-dd HH:mm:ss')
	,fnInfo.[backup_size_mb] 
	,fnInfo.[backup_size_gb] 
	,[backup_elapsed_time] = fnInfo.[backup_time]
	,bs.[type]
	,fnInfo.type_name
	,fnInfo.[path]
	,fnInfo.[file_name]
	,[description] = CONCAT('"',
		'Server = ', CAST(SERVERPROPERTY('Servername') AS sysname), fn.crlf, 
		'Database = ', bs.database_name, fn.crlf, 
		'Type = ', fnInfo.type_name, '(', bs.type, ')', fn.crlf,
		'Start Date = ', bs.backup_start_date, fn.crlf, 
		'End Date = ', bs.backup_finish_date, fn.crlf,
		'Size = ', fnSize.size, fn.crlf,
		'Path = ', fnInfo.path, fnInfo.file_name, fn.crlf, '"')
FROM msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bm ON (bs.media_set_id = bm.media_set_id)
CROSS APPLY (
	SELECT [last_back_slash] = CHARINDEX('\',REVERSE(bm.physical_device_name)) - 1 
		,crlf = CHAR(13) + CHAR(10)		 
		,[server] = CAST(SERVERPROPERTY('Servername') AS sysname)
) fn
CROSS APPLY (
	SELECT [path] = CASE WHEN fn.[last_back_slash] <= 0 THEN bm.physical_device_name ELSE LEFT(bm.physical_device_name, LEN(bm.physical_device_name) - fn.[last_back_slash]) END
		,[file_name] = CASE WHEN fn.[last_back_slash] <= 0 THEN '' ELSE REVERSE(LEFT(REVERSE(bm.physical_device_name), fn.[last_back_slash])) END
		,[backup_time] = CONVERT(VARCHAR(20),(bs.backup_finish_date - bs.[backup_start_date]), 108)
		,[backup_size_mb] = CAST(bs.backup_size/1000.0/1000.0 AS DECIMAL(18, 2))
		,[backup_size_gb] = CAST(bs.backup_size/1000.0/1000.0/1000.0 AS DECIMAL(18, 2))
		,[type_name] = CASE bs.[type]
			WHEN 'D' THEN 'Database'
			WHEN 'I' THEN 'Differential database'
			WHEN 'L' THEN 'Log'
			WHEN 'F' THEN 'File or filegroup'
			WHEN 'G' THEN 'Differential file'
			WHEN 'P' THEN 'Partial'
			WHEN 'Q' THEN 'Differential partial'
			ELSE 'Unknown'
		END
		,[server] = LEFT(fn.server, CHARINDEX('\', fn.server) - 1) 			
) fnInfo
CROSS APPLY (
	SELECT [size]  = CASE WHEN fnInfo.backup_size_gb > 0 THEN CONCAT(fnInfo.backup_size_gb, ' GB') ELSE CONCAT(fnInfo.backup_size_mb, ' MB') END
) fnSize
WHERE bs.backup_start_date > DATEADD(DAY, -14, GETDATE()) 
	AND LEN(fnInfo.file_name) > 0
	AND bs.type IN ('D', 'I') 
	--/* temp to check for fulls during the day */
	--AND bs.type = 'D'
	--AND CAST(bs.backup_start_date AS TIME) BETWEEN '08:00:00' AND '19:00:00' 
	--AND DATEPART(WEEKDAY, bs.backup_start_date) BETWEEN 2 AND 6 
	--/* temp to check for fulls during the day */
ORDER BY bs.database_name,
	bs.backup_start_date,
	bs.backup_finish_date