Tuesday, 26 March 2013

SQL Server Backup Status (Full & Transaction)

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'

No comments:

Post a Comment