我使用下面的 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
),然后将其加入您的查询:只是为了添加 Spaghettidba 的美丽答案:
如果您备份到多个文件怎么办?这个脚本会告诉你这一切。