数据库日常维护-CheckList_02有关数据库备份检查

数据库日常维护-CheckList_02有关数据库备份检查

--------------------------------------------------------------------------------- 

--前一周所有数据库备份情况

--------------------------------------------------------------------------------- 

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, 

   CASE msdb..backupset.type  

       WHEN 'D' THEN 'Database'  

       WHEN 'L' THEN 'Log'  

   END AS backup_type,  

   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  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  

ORDER BY  

   msdb.dbo.backupset.database_name, 

   msdb.dbo.backupset.backup_finish_date

 ------------------------------------------------------------------------------------------- 

--近期每个数据库的备份情况 

------------------------------------------------------------------------------------------- 

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  

ORDER BY  

   msdb.dbo.backupset.database_name

 

------------------------------------------------------------------------------------------- 

--近期每个数据库备份-详细情况

------------------------------------------------------------------------------------------- 

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')) 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

 ------------------------------------------------------------------------------------------- 

--丢失备份

------------------------------------------------------------------------------------------- 

--超过24小时的数据库备份

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, 

   DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)] 

FROM    msdb.dbo.backupset 

WHERE     msdb.dbo.backupset.type = 'D'  

GROUP BY msdb.dbo.backupset.database_name 

HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))  

 

UNION  

 

--没有任何备份历史  

SELECT      

   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,  

   master.dbo.sysdatabases.NAME AS database_name,  

   NULL AS [Last Data Backup Date],  

   9999 AS [Backup Age (Hours)]  

FROM 

   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset 

       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name 

WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb' 

ORDER BY  

   msdb.dbo.backupset.database_name

 ------------------------------------------------------------------------------------------- 

--检查所有备份文件大小 (GB)

------------------------------------------------------------------------------------------- 

SELECT   getdate()  as,

b.server_name, Round(SUM(convert(float,b.backup_size) /1024.0/1024.0/1024.0),2) AS 'backup_size_GB', 

Round(SUM(convert(float,b.compressed_backup_size)/1024.0/1024.0/1024.0),2) AS 'compressed_backup_size_GB' FROM msdb..backupset b 

where  b.database_name not in ('model','master','msdb','')

--and b.type='D'

AND backup_start_date>getdate()-1 

GROUP BY b.server_name  

------------------------------------------------------------------------------------------- 

 

 

SameZhao