SQL Server Database
Backup Status
Using below scripts we can find if a disk level backup has happend for a DB in last 7 days or not and we can check the status Transaction Log backup and we will get an entry if the Transaction Log is more than 10 GB.
For SQL Server 2000
select distinct @@servername [Server
Name],bs.database_name Bkup_DB_Name,db.name
[DB_Name],DATABASEPROPERTYEX(db.name,'Recovery')
recovery_model_desc,DATABASEPROPERTYEX(db.name,'Recovery') log_reuse_wait_desc,
case when bs.backup_finish_date is Null then 'No Latest Full Backup' when
bs.backup_finish_date>=(getdate()-7) then 'Latest Backup' when
bs.backup_finish_date<(getdate()-7) then 'Not Latest Backup' else 'Check Last
Backup of the Database' end [Backup Status], case when bs.database_name is
NULL then 'Take a full backup of '+db.name+' Database and check in Maintenance
Plan' else 'We have a full backup, check latest or not' end Message from
(msdb..backupmediafamily bm join msdb.dbo.backupset bs on
bs.media_set_id=bm.media_set_id and device_type=2 and
backup_finish_date>=(getdate()-7) ) right outer join master.dbo.sysdatabases
db on bs.database_name=db.name where db.name<>'tempdb' and
DATABASEPROPERTYEX(db.name,'status')='ONLINE' and (bs.database_name is null)
union select @@servername [Server Name],instance_name Perf_DB_Name,db.name
[DB_Name],DATABASEPROPERTYEX(instance_name,'Recovery')
recovery_model_desc,cntr_value, case when cntr_value>=80 then 'Transaction
Log usage is more than 80%' when cntr_value<79 then 'Transaction Log usage
is less than 80%' else 'check' end [Backup sattus], case when
cntr_value>=80 then 'Take a Transaction Log Backup of '+db.name+' Database
and check in Maintenance Plan' else 'Check Database' end [Message] from
master.dbo.sysperfinfo sp join master.dbo.sysdatabases db on
db.name=sp.instance_name and COUNTER_NAME LIKE '%Percent Log Used%' AND
INSTANCE_NAME NOT IN ('_Total','mssqlsystemresource') and cntr_value>=80
union select @@servername [Server Name],instance_name Perf_DB_Name,db.name
[DB_Name],DATABASEPROPERTYEX(instance_name,'Recovery')
recovery_model_desc,cntr_value/1024/1024 Counter_value, case when
(cntr_value/1024/1024)>=10 then 'Transaction Log usage is more than 10 GB'
when (cntr_value/1024/1024)<10 then 'Transaction Log usage is less than 10
GB' else 'check Database' end [Backup sattus], case when
(cntr_value/1024/1024)>=10 then 'Take a Transaction Log Backup of '+db.name+'
Database and check in Maintenance Plan' else 'Check Database' end [Message] from
master.dbo.sysperfinfo sp join master.dbo.sysdatabases db on
db.name=sp.instance_name and COUNTER_NAME LIKE '%Log File(s) Size (KB) %' AND
INSTANCE_NAME NOT IN ('_Total','mssqlsystemresource') and
(cntr_value/1024/1024)>=10
For SQL Server 2005 and 2008
select distinct @@servername [Server
Name],bs.database_name Bkup_DB_Name,db.name
[DB_Name],db.recovery_model_desc,db.log_reuse_wait_desc, case when
bs.backup_finish_date is Null then 'No Latest Full Backup' when
bs.backup_finish_date>=(getdate()-7) then 'Latest Backup' when
bs.backup_finish_date<(getdate()-7) then 'Not Latest Backup' else 'Check Last
Backup of the Database' end [Backup Status], case when bs.database_name is
NULL then 'Take a full backup of '+db.name+' Database and check in Maintenance
Plan' else 'We have a full backup, check latest or not' end Message from
(msdb..backupmediafamily bm join msdb.dbo.backupset bs on
bs.media_set_id=bm.media_set_id and device_type=2 and
backup_finish_date>=(getdate()-7) ) right outer join master.sys.databases db
on bs.database_name=db.name where db.name<>'tempdb' and
db.state_desc='ONLINE' and (bs.database_name is null) union select
@@servername,name,name,recovery_model_desc,log_reuse_wait_desc,case when
log_reuse_wait_desc='LOG_BACKUP' then 'Trans Log getting Full' else
log_reuse_wait_desc end,case when log_reuse_wait_desc='LOG_BACKUP' then 'Take
a Transaction Log Backup of '+name+' Database and check in Maintenance Plan'
else 'Check Database' end Message from master.sys.databases where
log_reuse_wait_desc='LOG_BACKUP'
