Revision de ultima fecha de respaldos.

Aunque ya habia publicado un script antes, no funcionaba con las nuevas versiones de SQL Server, este nuevo script ademas de funcionar en la
s nuevas versiones, muestra diversos tipos de respaldo, como son el full, differential y el log.

/* Created by Fernando Rivas @ dbamex.blogspot.com */


--Create temporary table to hold the information.
Create table #temp_bkp_detail
(DB_Name varchar (512), 
status varchar(32),
Full_BKP varchar (64),
Diff_BKP varchar (64),
Trn_BKP varchar (64)
)


--Gather all backups information, retrieve only latest ones. 
select database_name, Type, max(backup_finish_date) as last_date into #temp_bkp
from msdb.dbo.backupset
group by database_name, type


--gather all db names but tempdb. 
insert into #temp_bkp_detail(DB_Name, status)
select name, state_desc from master.sys.databases
where name != 'tempdb'


--update backup info in details table 
update #temp_bkp_detail 
set Full_BKP = last_date 
from #temp_bkp 
where database_name = DB_NAME 
and Type = 'D'

update #temp_bkp_detail 
set Diff_BKP = last_date 
from #temp_bkp 
where database_name = DB_NAME 
and Type = 'I'

update #temp_bkp_detail 
set Trn_BKP = last_date 
from #temp_bkp 
where database_name = DB_NAME 
and Type = 'L'


select * from #temp_bkp_detail

drop table #temp_bkp_detail
drop table #temp_bkp


/* Created by Fernando Rivas @ dbamex.blogspot.com */