max restore date 1

[peac3]

Hi guys,

I am trying to find the max restore date distinct per database name.

here's my query at the moment

use msdb;
select 
      DBRestored  = h.destination_database_name,
      RestoreDate = h.restore_date,
      SourceDB    = b.database_name,
      SourceFile  = f.physical_name,
      BackupDate  = b.backup_start_date
      
from restorehistory h
inner join backupset b
      on h.backup_set_id = b.backup_set_id
inner join backupfile f
      on f.backup_set_id = b.backup_set_id
order by DBRestored,RestoreDate desc

and the result is

DBRestored	RestoreDate	SourceDB	SourceFile	BackupDate
database1	2012-02-07 13:25:19.187	database1	H:\database1.mdf	2012-01-31 19:14:16.000
database1	2012-02-07 13:25:19.187	database1	H:\database1.LDF	2012-01-31 19:14:16.000
database1	2012-02-01 15:21:47.760	database1	H:\database1.mdf	2012-01-31 19:14:16.000
database1	2012-02-01 15:21:47.760	database1	H:\database1.LDF	2012-01-31 19:14:16.000
database1	2012-01-06 10:43:58.163	database1	H:\database1.mdf	2011-11-30 19:14:19.000
database1	2012-01-06 10:43:58.163	database1	H:\database1.LDF	2011-11-30 19:14:19.000
database2	2012-02-07 13:29:41.387	database2	H:\database2.mdf	2012-01-31 19:15:57.000
database2	2012-02-07 13:29:41.387	database2	H:\database2.LDF	2012-01-31 19:15:57.000
database2	2012-02-01 15:21:47.760	database2	H:\database2.mdf	2012-01-31 19:15:57.000
database2	2012-02-01 15:21:47.760	database2	H:\database2.LDF	2012-01-31 19:15:57.000
database2	2012-01-06 10:45:46.337	database2	H:\database2.mdf	2011-11-30 19:16:00.000
database2	2012-01-06 10:45:46.337	database2	H:\database2.LDF	2011-11-30 19:16:00.000
database3	2012-02-07 13:19:58.870	database3	H:\database3.mdf	2012-01-31 19:00:06.000
database3	2012-02-07 13:19:58.870	database3	H:\database3_log.LDF	2012-01-31 19:00:06.000
database3	2012-02-01 15:25:36.457	database3	H:\database3.mdf	2012-01-31 19:00:06.000
database3	2012-02-01 15:25:36.457	database3	H:\database3_log.LDF	2012-01-31 19:00:06.000
database3	2012-01-06 10:43:58.163	database3	H:\database3.mdf	2011-11-30 19:00:09.000

and I would like to have the result only with the latest restore date distinct per database name like this

database1	2012-02-07 13:25:19.187	database1	H:\database1.mdf	2012-01-31 19:14:16.000
database2	2012-02-07 13:29:41.387	database2	H:\database2.mdf	2012-01-31 19:15:57.000
database3	2012-02-07 13:19:58.870	database3	H:\database3.mdf	2012-01-31 19:00:06.000

Any input will be much appreciated.

Thanks guys,

 

[bborissov]

use msdb;

SELECT * FROM (
select
      DBRestored  = h.destination_database_name,
      RestoreDate = h.restore_date,
      SourceDB    = b.database_name,
      SourceFile  = f.physical_name,
      BackupDate  = b.backup_start_date,
      ROW_NUMBER() OVER (PARTITION BY destination_database_name ORDER BY restore_date DESC) AS Test
      
from restorehistory h
inner join backupset b
      on h.backup_set_id = b.backup_set_id
inner join backupfile f
      on f.backup_set_id = b.backup_set_id
) Tst
WHERE Test = 1
order by DBRestored

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.