T-SQL query for date of last full backup, size and location

Question

I'm using the below T-SQL query to determine the date of the last full database backup and also return the size and location of the backup file. My problem is that it won't return any data at all for databases that have had no backups or for which there is no backup history for. Ideally, I'd want to modify the query so that all databases are returned, regardless of whether or not there's any backup history for them. Can anyone advise how the below query could be modified to accommodate this?

WITH LastBackUp AS
(
SELECT bs.database_name,
 bs.backup_size,
 bs.backup_start_date,
 bmf.physical_device_name,
 Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
FROM msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE bs.[type] = 'D'
AND bs.is_copy_only = 0
)
SELECT 
 database_name AS [Database],
 CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB],
 backup_start_date AS [Last Full DB Backup Date],
 physical_device_name AS [Backup File Location]
FROM LastBackUp
WHERE Position = 1
ORDER BY [Database];

Answer 1

You need to query the list of databases first (it's in sys.databases), then join it to your query:

WITH LastBackUp AS
(
SELECT bs.database_name,
 bs.backup_size,
 bs.backup_start_date,
 bmf.physical_device_name,
 Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
FROM msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE bs.[type] = 'D'
AND bs.is_copy_only = 0
)
SELECT 
 sd.name AS [Database],
 CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB],
 backup_start_date AS [Last Full DB Backup Date],
 physical_device_name AS [Backup File Location]
FROM sys.databases AS sd
LEFT JOIN LastBackUp AS lb
 ON sd.name = lb.database_name
 AND Position = 1
ORDER BY [Database];

Answer 2

Just to add to the beautiful answer by Spaghettidba:

what if you backup to more than one file? this script will show you it all.

 declare @backup_type CHAR(1) = 'D' --'D' full, 'L' log
 ;with Radhe as (
 SELECT @@Servername as [Server_Name],
 B.name as Database_Name, 
 ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
 ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate
 ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2))
 ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
 ,media_set_id = MAX(A.media_set_id)
 ,[AVG Backup Duration]= AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
 ,[Longest Backup Duration]= MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
 ,A.type
 FROM sys.databases B 
 LEFT OUTER JOIN msdb.dbo.backupset A 
 ON A.database_name = B.name 
 AND A.is_copy_only = 0
 AND (@backup_type IS NULL OR A.type = @backup_type )
 GROUP BY B.Name, A.type
 )
 SELECT r.[Server_Name]
 ,r.Database_Name
 ,[Backup Type] = r.type 
 ,r.DaysSinceLastBackup
 ,r.LastBackupDate
 ,r.BackupSize_GB
 ,r.BackupSize_MB
 ,F.physical_device_name
 ,r.[AVG Backup Duration]
 ,r.[Longest Backup Duration]
 FROM Radhe r
 LEFT OUTER JOIN msdb.dbo.backupmediafamily F
 ON R.media_set_id = F.media_set_id
 ORDER BY r.Server_Name, r.Database_Name

• Marcello Miorelli: I suggest you post a question here and quote this question and add the details that you get and show how they don't match. it would be an interesting question and other DBAs can benefit.