I have a production database that is fully backed up nightly and incrementally backed up hourly. On our backup machine, we have the differential backups and full backups restored ion a regular basis in a job. However, I need to only restore a backup file if the backup is valid (meaning this backup is newer than the current database backup version). Is there any way to accomplish this?

This is our current solution until we can get mirroring working.

All help is greatly appreciated!


There is an msdb table that contains the restore history:

select  * 
from    msdb.dbo.restorehistory rh

For a database, you can look up the date of the last restore. If you can determine the date of the incoming restore (typically the restore date is part of the file name) you could compare the two.

While this safeguards against overwriting newer restores with older ones, it's not ideal. There can be a difference between the backup date and the restore date, and newer restores that fall in between will be ignored.

You could improve that if you knew the filename that the last restore came from, but I can't find that information in the msdb tables.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy