T-SQL 查詢上次完整備份的日期、大小和位置

我使用下面的 T-SQL 查詢來確定上次完整數據庫備份的日期，並返回備份文件的大小和位置。我的問題是，對於沒有備份或沒有備份歷史記錄的數據庫，它根本不會返回任何數據。理想情況下，我想修改查詢以便返回所有數據庫，無論它們是否有任何備份歷史記錄。誰能建議如何修改以下查詢以適應此情況？

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

您需要先查詢數據庫列表（位於 中sys.databases），然後將其加入您的查詢：

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

引用自：https://dba.stackexchange.com/questions/150241