Backups per Database (Pivot).sql

WITH BackupsPerDatabase AS
(
	SELECT  
	   --CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
	   db.database_id,
	   bs.type,
	   MAX(bs.backup_finish_date) AS last_db_backup_date 
	FROM sys.databases db 
	   LEFT OUTER JOIN msdb.dbo.backupset bs ON db.name = bs.database_name
	   INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
	GROUP BY db.database_id, bs.type
), BackupsPerDatabase2 AS
(
	SELECT * FROM BackupsPerDatabase
	PIVOT
	(
		MAX(last_db_backup_date)
		FOR [type] in ([D], [I], [L]) --, [F], [G], [P], [Q])
	) as pvt
)
SELECT @@SERVERNAME server, db.name dbname, db.create_date, db.recovery_model_desc,b.D, b.I, b.L
FROM BackupsPerDatabase2 b
INNER JOIN sys.databases db on db.database_id = b.database_id
ORDER BY 1, 2


-- https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql?view=sql-server-2017
--type
-- 	Backup type. Can be:
--	D = Database
--	I = Differential database
--	L = Log
--	F = File or filegroup
--	G = Differential file
--	P = Partial
--	Q = Differential partial
--	Can be NULL.

Check database backups.sql

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   db.name,
   bs.type,
   MAX(bs.backup_finish_date) AS last_db_backup_date 
FROM sys.databases db 
   LEFT OUTER JOIN msdb.dbo.backupset bs ON db.name = bs.database_name
   INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
GROUP BY db.name, bs.type
ORDER BY db.name, bs.type

-- https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql?view=sql-server-2017
--type
-- 	Backup type. Can be:
--	D = Database
--	I = Differential database
--	L = Log
--	F = File or filegroup
--	G = Differential file
--	P = Partial
--	Q = Differential partial
--	Can be NULL.