Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 11 April 2014

Script To Check Last Full Backup Duration of All Database

Script To Check Last Full Backup Duration of All Database:

set nocount on
if exists ( select name from tempdb..sysobjects where name like '#DatabasesBackupsDuration%')
drop table #DatabasesBackupsDuration
create table #DatabasesBackupsDuration
ServerName varchar(100) null,
DBName varchar(100) null,
RecoveryModel varchar(100) null,
LastFullbackup datetime null,
FullbackupDurationSec bigint null,
DBStatus varchar (100) null,

insert into #DatabasesBackupsDuration(ServerName,DBName)
select convert(varchar,serverproperty('ServerName')), 
from master.dbo.sysdatabases a
where <> 'tempdb'

update #DatabasesBackupsDuration
set LastFullbackup=b.backup_start_date
from #DatabasesBackupsDuration a,(select database_name,max(backup_start_date) backup_start_date 
from msdb..backupset  where type='D' group by database_name)b
where a.DBName=b.database_name

update #DatabasesBackupsDuration 
set RecoveryModel=convert(sysname,DatabasePropertyEx(DBName,'Recovery'))

update #DatabasesBackupsDuration 
set DBStatus=convert(sysname,DatabasePropertyEx(DBName,'Status'))

update d
set d.FullbackupDurationSec = datediff(s,backup_start_date, backup_finish_date)
from #DatabasesBackupsDuration d,(select database_name, max(backup_start_date) as backup_start_date, max(backup_finish_date) as backup_finish_date from msdb..backupset 
where type ='D' group by database_name) b where d.DBName = b.database_name
select * from #DatabasesBackupsDuration order by LastFullbackup
select CAST(SUM(FullbackupDurationSec)/60 AS varchar(100))+' Minutes' As FullBackupTimeTotal from #DatabasesBackupsDuration
drop table #DatabasesBackupsDuration