SQL server 查看各种备份情况的脚本

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,
   CASE msdb.dbo.backupset.type
       WHEN 'D' THEN 'Full'
    WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'
   END backup_type,
  msdb.dbo.backupset.backup_size,
  msdb.dbo.backupmediafamily.logical_device_name,
  msdb.dbo.backupmediafamily.physical_device_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.dbo.backupset.backup_start_date>dateadd(mi,-70,getdate()) and msdb.dbo.backupset.type in ('L')
--msdb.dbo.backupset.backup_start_date>dateadd(mi,-2880,getdate()) and msdb.dbo.backupset.type in ('D')
 and msdb.dbo.backupset.database_name in ('HDWLERP','ky_member','ky_proxy','KY_Template','KY2014','kycenter','KYDisplay','WEB2011')  --and
.dbo.backupset.database_name in ('HX_WEB2011','HXWLERP','HX2013')  
--and msdb.dbo.backupset.database_name in ('ky2012','web2011','wlerp')  
--and msdb.dbo.backupset.database_name in ('ky2011') 
ORDER BY
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date desc

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29507357/viewspace-1761930/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29507357/viewspace-1761930/