Skip to main content
3 of 3
added 7 characters in body

Restore Verify Only w/ Transaction Log Backups

Question When RESTORE VERIFY ONLY on a transaction log backup fails, what do you do?

Background After FULL and DIFFERENTIAL backups, I run RESTORE VERIFY ONLY on the backup file(s). This rarely ever fails. But when it does, I simply re-run the backup, and re-run RESTORE VERIFY ONLY. Currently, I don't verify transaction log backups. It's an oversight on my part that I'm looking to correct. When I get to that point, I have to be prepared for all outcomes.

Possible Scenario Let's say I have a full backup with trx log backups performed periodically afterwards. If there are 10 trx log backups, but #7 failed a RESTORE VERIFY ONLY, the best I could do in a DR situation is to restore the FULL backup and six subsequent trx log backups. (I write this based on my understanding that when RESTORE VERIFY ONLY fails, RESTORE DATABASE...FROM <7th Trx Log Backup> will fail too.)

So, even though the trx log chain is not technically broken, it's "effectively" broken since I can't restore the 7th trx log backup (or any subsequent trx log backup).

If I had taken a DIFFERENTIAL backup immediately after #7 failed, in a DR situation I could:

  1. Restore the FULL backup
  2. Restore the DIFFERENTIAL backup
  3. Restore transaction log backups 8, 9, and 10