检索SQL Server数据库备份记录

核心提示： -----MostRecentDatabaseBackupforEachDatabase-Detailed---SELECTA.[Server],A.last_db_backup_date,B.backup_start_date,B.expiration_date,B.backup_siz

　　-------------------------------------------------------------------------------------------
　　--MostRecentDatabaseBackupforEachDatabase-Detailed
　　-------------------------------------------------------------------------------------------
　　SELECT
　　A.[Server],
　　A.last_db_backup_date,
　　B.backup_start_date,
　　B.expiration_date,
　　B.backup_size,
　　B.logical_device_name,
　　B.physical_device_name,
　　B.backupset_name,
　　B.description
　　FROM
　　(
　　SELECT
　　CONVERT(CHAR(100),SERVERPROPERTY('Servername'))ASServer,
　　msdb.dbo.backupset.database_name,
　　MAX(msdb.dbo.backupset.backup_finish_date)ASlast_db_backup_date
　　FROMmsdb.dbo.backupmediafamily
　　INNERJOINmsdb.dbo.backupsetONmsdb.dbo.backupmediafamily.media_set_id=msdb.dbo.backupset.media_set_id
　　WHEREmsdb..backupset.type='D'
　　GROUPBY
　　msdb.dbo.backupset.database_name
　　)ASA
　　LEFTJOIN
　　(
　　SELECT
　　CONVERT(CHAR(100),SERVERPROPERTY('Servername'))ASServer,
　　msdb.dbo.backupset.database_name,
　　msdb.dbo.backupset.backup_start_date,
　　msdb.dbo.backupset.backup_finish_date,
　　msdb.dbo.backupset.expiration_date,
　　msdb.dbo.backupset.backup_size,
　　msdb.dbo.backupmediafamily.logical_device_name,
　　msdb.dbo.backupmediafamily.physical_device_name,
　　msdb.dbo.backupset.nameASbackupset_name,
　　msdb.dbo.backupset.description
　　FROMmsdb.dbo.backupmediafamily
　　INNERJOINmsdb.dbo.backupsetONmsdb.dbo.backupmediafamily.media_set_id=msdb.dbo.backupset.media_set_id
　　WHEREmsdb..backupset.type='D'
　　)ASB
　　ONA.[server]=B.[server]ANDA.[database_name]=B.[database_name]ANDA.[last_db_backup_date]=B.[backup_finish_date]
　　ORDERBY
　　A.database_name