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 */
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 */
Comentarios
Publicar un comentario