We received an email on the value of this tip and wanted to pass along the comments...
Here is a comment from Jerry L. on this tip:
set nocount off
go
drop procedure getDBRestoreDate
go
create procedure getDBRestoreDate
@DatabaseName nvarchar(255)
,@DatabaseRestoreType varchar(4) -- Use 'Full','Inc','Log' to set what kind of restores to look at when getting the restore date
as
begin
--drop table #restore_list
SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore],
case bs.type when 'D' then 'Full' when 'I' then 'Inc' when 'L' then 'Log' else 'Unk' end as [backup_type]
into #restore_list
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
INNER JOIN sys.databases d on rs.[destination_database_name]=d.name and d.name = @DatabaseName -- if the database doesn't exist, return null
ORDER BY [rs].[restore_date] DESC
--drop table #restore_active_list
select max([restore_date]) as restore_date, [destination_database_name], [backup_type]
into #restore_active_list
from #restore_list
group by [destination_database_name], [backup_type]
--select * from #restore_active_list order by [destination_database_name],restore_date
delete rl1
--select *
from #restore_active_list rl1, #restore_active_list rl2
where rl2.backup_type='Full'
and rl1.restore_date < rl2.restore_date
and rl1.[destination_database_name]=rl2.[destination_database_name]
-- any restore prior to the most recent full does not count
delete rl1
--select *
from #restore_active_list rl1, #restore_active_list rl2
where rl2.backup_type='Inc' and rl1.backup_type='Log'
and rl1.restore_date < rl2.restore_date
and rl1.[destination_database_name]=rl2.[destination_database_name]
-- any 'Log' restore prior to the most recent 'Inc' doesn't count
select 'Full' as ToRestore,'Full' as FromRestore, 0 LogLogMatch
into #To_From_Trade
union all
select 'Inc','Full',0
union all
select 'Inc','Inc',0
union all
select 'Log','Full',0
union all
select 'Log','Inc',0
union all
select 'Log','Log',1
select max(rl.backup_start_date) as max_backup_start_date
,max(T.LogLogMatch) as LogLogMatch
--*
from #restore_active_list arl, #restore_list rl, #To_From_Trade T
where arl.restore_date=rl.restore_date
and arl.[destination_database_name]=rl.[destination_database_name]
and arl.[backup_type]=rl.[backup_type]
and T.ToRestore=@DatabaseRestoreType and T.FromRestore=arl.backup_type
--order by 1 desc
end
go
Now I can do
exec getDBRestoreDate '<Database Name>', 'FULL'
to get the date of the most recent full for my scripts that restores fulls.
Also, my log-shipping scripts use
exec getDBRestoreDate '<Database Name>', 'LOG'
to get the date of the most recent restore, and then they compare that to the timestamps on the files to find which one if any to restore next.
also, here is a modified version I use for reporting.
go
set nocount off
go
drop procedure getDBRestoreDates
go
create procedure getDBRestoreDates
as
begin
--drop table #restore_list
--get all the restores with right data attached
SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore],
case bs.type when 'D' then 'Full' when 'I' then 'Inc' when 'L' then 'Log' else 'Unk' end as [backup_type]
into #restore_list
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
INNER JOIN sys.databases d on rs.[destination_database_name]=d.name
ORDER BY [rs].[restore_date] DESC
--select * from #restore_list order by 1,2
--drop table #restore_active_list
--get a list of the last of each type for each db
select max([restore_date]) as restore_date, [destination_database_name], [backup_type]
into #restore_active_list
from #restore_list
group by [destination_database_name], [backup_type]
--select * from #restore_active_list order by [destination_database_name],restore_date
--remove the old restores
delete rl
from #restore_list rl
inner join #restore_active_list ral
on rl.[destination_database_name]=ral.[destination_database_name]
and rl.[backup_type]=ral.[backup_type]
and rl.restore_date<ral.restore_date
-- any restore prior to the most recent full does not count
delete rl1
--select *
from #restore_active_list rl1, #restore_active_list rl2
where rl2.backup_type='Full'
and rl1.restore_date < rl2.restore_date
and rl1.[destination_database_name]=rl2.[destination_database_name]
-- any 'Log' restore prior to the most recent 'Inc' doesn't count
delete rl1
--select *
from #restore_active_list rl1, #restore_active_list rl2
where rl2.backup_type='Inc' and rl1.backup_type='Log'
and rl1.restore_date < rl2.restore_date
and rl1.[destination_database_name]=rl2.[destination_database_name]
--If you are looking for the most recent time of a certain type of restore, certain other types of restore can override
select 'Full' as ToRestore,'Full' as FromRestore, 0 LogLogMatch
into #To_From_Trade
union all
select 'Inc','Full',0
union all
select 'Inc','Inc',0
union all
select 'Log','Full',0
union all
select 'Log','Inc',0
union all
select 'Log','Log',1
select
arl.[destination_database_name]
--, T.ToRestore
, arl.[backup_type]
, max(rl.restore_date) restore_date
, max(rl.backup_start_date) backup_start_date
, max(rl.backup_finish_date) backup_finish_date
, min(rl.backup_file_used_for_restore) as ex_backup_file_used_for_restore
from #restore_active_list arl
, #restore_list rl
--, #To_From_Trade T
where arl.restore_date=rl.restore_date
and arl.[destination_database_name]=rl.[destination_database_name]
and arl.[backup_type]=rl.[backup_type]
--and T.FromRestore=arl.backup_type
group by arl.[destination_database_name]
--, T.ToRestore
, arl.[backup_type]
order by 1,2,3
end
go
exec getDBRestoreDates
I have a reporting server, and advanced users of the server often want to know what time the data is from. This generates data for the report I'm using now.
