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 = 'email@example.com', @subject = 'AdventureWorks was restored from an old backup';