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];