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
go
if exists ( select name from tempdb..sysobjects where name like '#DatabasesBackupsDuration%')
drop table #DatabasesBackupsDuration
go
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,

)
go
insert into #DatabasesBackupsDuration(ServerName,DBName)
select convert(varchar,serverproperty('ServerName')),a.name 
from master.dbo.sysdatabases a
where a.name <> '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
go
select * from #DatabasesBackupsDuration order by LastFullbackup
go
select CAST(SUM(FullbackupDurationSec)/60 AS varchar(100))+' Minutes' As FullBackupTimeTotal from #DatabasesBackupsDuration
go
drop table #DatabasesBackupsDuration