SQLServer备份信息查看

查看数据文件和日志文件的备份记录

SELECT [filegroup_name]
      ,[backed_up_page_count]
      ,[file_type]
      ,[file_size]
      ,[logical_name]
      ,[physical_name]
      ,[State]
      ,[state_desc]
      ,[backup_size]
      ,[differential_base_lsn]
 FROM [msdb].[dbo].[backupfile];

其中：
filegroup_name：日志文件没有文件组，故日志文件备份信息中，该列为null
file_type：D表示数据文件；L表示日志文件；F表示full-text catalog；S表示内存优化文件
State：0表示online；1表示restoring；2表示recovering；3表示recover pending；4表示suspect；6表示offline；7表示defunct；8表示dropped

备份后查看备份的逻辑和物理设备名称

select logical_device_name ,physical_device_name,device_type  FROM [msdb].[dbo].[backupmediafamily]; 

其中：
device_type：2表示磁盘；5表示磁带；7表示虚拟设备；9表示azure存储；105表示A permanent backup SQL database device

查看每个数据库的备份信息

select name,user_name,first_lsn,last_lsn,database_backup_lsn,backup_start_date,backup_finish_date,type,database_name,server_name,machine_name 
FROM [msdb].[dbo].[backupset];

查看最近的全备信息

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  msdb..backupset.type = 'D' 
GROUP BY 
   msdb.dbo.backupset.database_name;

查看数据库的历史备份信息

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
When 'I' THEN 'Differential database'
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
-- WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE()-1 ) 
ORDER BY 
msdb.dbo.backupset.backup_finish_date desc

查看哪些数据库没有做备份

SELECT      
      S.NAME AS database_name,  
      'Nobackups' AS [Backup Age (Hours)]  
FROM 
   master.dbo.sysdatabases S LEFT JOIN msdb.dbo.backupset B
       ON S.name  = B.database_name 
WHERE B.database_name IS NULL AND S.name <> 'tempdb'
ORDER BY  
   B.database_name;

其他

SELECT  
   A.[Server],   
   A.database_name,  
   A.last_db_backup_date,  
   B.backup_start_date,    
   B.backup_finish_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')) AS Server, 
      msdb.dbo.backupset.database_name,  
      MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
   FROM 
      msdb.dbo.backupmediafamily  
      INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
   WHERE 
      msdb..backupset.type = 'D' 
   GROUP BY 
      msdb.dbo.backupset.database_name  
   ) AS A 
   LEFT JOIN  
   ( 
   SELECT   
      CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
      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.name AS backupset_name, 
      msdb.dbo.backupset.description 
   FROM 
      msdb.dbo.backupmediafamily  
      INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
   WHERE 
      msdb..backupset.type = 'D' 
   ) AS B 
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] 
ORDER BY  
   A.database_name