Skip to main content
Database Administrators

Return to Question

added 7 characters in body
Source Link
Fuck StackOverflow
Fuck StackOverflow
  • 875
  • 1
  • 8
  • 20

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 soonimmediately 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

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 soon 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

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
Rewrote question with more details to specify what it is I'm looking for.
Source Link
Fuck StackOverflow
Fuck StackOverflow
  • 875
  • 1
  • 8
  • 20

AfterQuestion 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). ThisThis rarely ever fails. ButBut when it does, I simply re-run the backup, and re-run RESTORE VERIFY ONLY.

Currently Currently, I don't verify transaction log backups. It'sIt's an oversight on my part that I'm looking to correct. But this brings up a question about how When I get to proceed whenthat 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 fails. Let's say, the best I could do in a DR situation is to restore the FULL backup and six subsequent trx log backup completes successfully, butbackups. (I write this based on my understanding that when RESTORE VERIFY ONLY fails. The backup spans LSNs X to Y, RESTORE DATABASE...FROM <7th Trx Log Backup> will fail too. I can't simply take another)

So, even though the trx log backup to "fix"chain is not technically broken, it's "effectively" broken since I can't restore the situation: the subsequent7th trx log backup would span a different range of LSNs (Y+1 to Zor any subsequent trx log backup). At this point, the log chain is effectively broken, right?

My first thought is to immediately takeIf I had taken a DIFFERENTIAL backup. What would you do? soon 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

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. But this brings up a question about how to proceed when RESTORE VERIFY ONLY fails. Let's say a trx log backup completes successfully, but RESTORE VERIFY ONLY fails. The backup spans LSNs X to Y. I can't simply take another trx log backup to "fix" the situation: the subsequent trx log backup would span a different range of LSNs (Y+1 to Z). At this point, the log chain is effectively broken, right?

My first thought is to immediately take a DIFFERENTIAL backup. What would you do?

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 soon 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
Source Link
Fuck StackOverflow
Fuck StackOverflow
  • 875
  • 1
  • 8
  • 20

Restore Verify Only w/ Transaction Log Backups

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. But this brings up a question about how to proceed when RESTORE VERIFY ONLY fails. Let's say a trx log backup completes successfully, but RESTORE VERIFY ONLY fails. The backup spans LSNs X to Y. I can't simply take another trx log backup to "fix" the situation: the subsequent trx log backup would span a different range of LSNs (Y+1 to Z). At this point, the log chain is effectively broken, right?

My first thought is to immediately take a DIFFERENTIAL backup. What would you do?