Taste the freshness of a restore.

I struggled for ages to find the simplest way to handle a situation where a backup file may or may not have been uploaded to an FTP site for restoring (see my last, which worked fine – but failed to produce the desired error message when the folder was empty).

As it only took a few minutes to restore this particular database, it was acceptable to blindly restore it and then decide if doing so had been useful. For this I made use of the backup_set_id column in the restorehistory table within MSDB.

This column appears to contain a check-sum for every restore, and therefore it was easy enough to see if the last two restores had the same value. Here’s the full job-step code …

select top 2 backup_set_id
into #temp
from restorehistory
where destination_database_name = 'AdventureWorks'
order by restore_history_id desc

if
(select max(backup_set_id) from #temp)
 =
(select min(backup_set_id) from #temp)
exec sp_send_dbmail
 @recipients = 'somebody@somewhere.com',
 @subject = 'AdventureWorks was restored from an old backup';