Return to Answer

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

Restore verifyonly(as per BOL)Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. So even if backup set comes as verified clean by Verifyonly its not 100% guaranteed that backup set is consistent only a successful restore of a backup can guarantee that backup set is valid.

I have not see an scenario where verifyonly fails but restores succeeds(unless you succeed with using continue_after_error) it can only happen when verifyonly failed saying there is not enough space to check backup consistency because verifyonly also checks that enough space is there to restore the database

Checks performed by RESTORE VERIFYONLY include:

•That the backup set is complete and all volumes are readable.

•Some header fields of database pages, such as the page ID (as if it were about to write the data).

•Checksum (if present on the media).

•Checking for sufficient space on destination devices.

Please show me the message which you got when verifyonly failed

After Verifyonly fails

I would run a checkdb on my database to check for any inconsistency but point here to note is checkdb does not do consistency check for log file only when recovery on snapshot is run during checkdb log file is used so we 'can' say that it checks for log file as well but not complete like it does for data file. If checkdb comes out clean I would say my database is consistent.

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?

Yes you are correct taking multiple backup does not fixes corruption in log backup but taking multiple log backups does not break log chain. Log files are inked internally with LSN number and taking multiple log backup does not breaks chain if log backups are restored in sequential manner

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

It wont solve corruption issue in any way. Differential backups are means to lower the RTO and perform speedy recovery of database thats it.

EDIT AFTER USER UPDATED HIS QUESTION:

the best I could do in a DR situation is to restore the FULL backup and six subsequent trx log backups

Yes you are correct.

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

Restore the FULL backup Restore the DIFFERENTIAL backup Restore transaction log backups 8, 9, and 10 

No you can restore differential backup, if it allows you to restore but 8,9,10 logs backups were taken before the differential backup not after it and when you restore log backup 8 it will try to match LSN with Log 7 which is not restore. So you cannot restore it anyways there is no point in restoring these logs as differential backup MIGHT have included those changes

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

Restore verifyonly(as per BOL)Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. So even if backup set comes as verified clean by Verifyonly its not 100% guaranteed that backup set is consistent only a successful restore of a backup can guarantee that backup set is valid.

I have not see an scenario where verifyonly fails but restores succeeds(unless you succeed with using continue_after_error) it can only happen when verifyonly failed saying there is not enough space to check backup consistency because verifyonly also checks that enough space is there to restore the database

Checks performed by RESTORE VERIFYONLY include:

•That the backup set is complete and all volumes are readable.

•Some header fields of database pages, such as the page ID (as if it were about to write the data).

•Checksum (if present on the media).

•Checking for sufficient space on destination devices.

Please show me the message which you got when verifyonly failed

After Verifyonly fails

I would run a checkdb on my database to check for any inconsistency but point here to note is checkdb does not do consistency check for log file only when recovery on snapshot is run during checkdb log file is used so we 'can' say that it checks for log file as well but not complete like it does for data file. If checkdb comes out clean I would say my database is consistent.

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?

Yes you are correct taking multiple backup does not fixes corruption in log backup but taking multiple log backups does not break log chain. Log files are inked internally with LSN number and taking multiple log backup does not breaks chain if log backups are restored in sequential manner

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

It wont solve corruption issue in any way. Differential backups are means to lower the RTO and perform speedy recovery of database thats it.

EDIT AFTER USER UPDATED HIS QUESTION:

the best I could do in a DR situation is to restore the FULL backup and six subsequent trx log backups

Yes you are correct.

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

Restore the FULL backup Restore the DIFFERENTIAL backup Restore transaction log backups 8, 9, and 10 

Yes you can restore differential backup, after you cleared question with details I have updated the answer. You can restore differential backup and then restore log backups 8,9,10 as they are taken after diff backup

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

Restore verifyonly(as per BOL)Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. So even if backup set comes as verified clean by Verifyonly its not 100% guaranteed that backup set is consistent only a successful restore of a backup can guarantee that backup set is valid.

I have not see an scenario where verifyonly fails but restores succeeds(unless you succeed with using continue_after_error) it can only happen when verifyonly failed saying there is not enough space to check backup consistency because verifyonly also checks that enough space is there to restore the database

Checks performed by RESTORE VERIFYONLY include:

•That the backup set is complete and all volumes are readable.

•Some header fields of database pages, such as the page ID (as if it were about to write the data).

•Checksum (if present on the media).

•Checking for sufficient space on destination devices.

Please show me the message which you got when verifyonly failed

After Verifyonly fails

I would run a checkdb on my database to check for any inconsistency but point here to note is checkdb does not do consistency check for log file only when recovery on snapshot is run during checkdb log file is used so we 'can' say that it checks for log file as well but not complete like it does for data file. If checkdb comes out clean I would say my database is consistent.

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?

Yes you are correct taking multiple backup does not fixes corruption in log backup but taking multiple log backups does not break log chain. Log files are inked internally with LSN number and taking multiple log backup does not breaks chain if log backups are restored in sequential manner

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

It wont solve corruption issue in any way. Differential backups are means to lower the RTO and perform speedy recovery of database thats it.

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

Restore verifyonly(as per BOL)Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. So even if backup set comes as verified clean by Verifyonly its not 100% guaranteed that backup set is consistent only a successful restore of a backup can guarantee that backup set is valid.

I have not see an scenario where verifyonly fails but restores succeeds(unless you succeed with using continue_after_error) it can only happen when verifyonly failed saying there is not enough space to check backup consistency because verifyonly also checks that enough space is there to restore the database

Checks performed by RESTORE VERIFYONLY include:

•That the backup set is complete and all volumes are readable.

•Some header fields of database pages, such as the page ID (as if it were about to write the data).

•Checksum (if present on the media).

•Checking for sufficient space on destination devices.

Please show me the message which you got when verifyonly failed

After Verifyonly fails

I would run a checkdb on my database to check for any inconsistency but point here to note is checkdb does not do consistency check for log file only when recovery on snapshot is run during checkdb log file is used so we 'can' say that it checks for log file as well but not complete like it does for data file. If checkdb comes out clean I would say my database is consistent.

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?

Yes you are correct taking multiple backup does not fixes corruption in log backup but taking multiple log backups does not break log chain. Log files are inked internally with LSN number and taking multiple log backup does not breaks chain if log backups are restored in sequential manner

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

It wont solve corruption issue in any way. Differential backups are means to lower the RTO and perform speedy recovery of database thats it.

EDIT AFTER USER UPDATED HIS QUESTION:

the best I could do in a DR situation is to restore the FULL backup and six subsequent trx log backups

Yes you are correct.

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

Restore the FULL backup Restore the DIFFERENTIAL backup Restore transaction log backups 8, 9, and 10 

No you can restore differential backup, if it allows you to restore but 8,9,10 logs backups were taken before the differential backup not after it and when you restore log backup 8 it will try to match LSN with Log 7 which is not restore. So you cannot restore it anyways there is no point in restoring these logs as differential backup MIGHT have included those changes

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

Restore verifyonly(as per BOL)Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. So even if backup set comes as verified clean by Verifyonly its not 100% guaranteed that backup set is consistent only a successful restore of a backup can guarantee that backup set is valid.

I have not see an scenario where verifyonly fails but restores succeeds(unless you succeed with using continue_after_error) it can only happen when verifyonly failed saying there is not enough space to check backup consistency because verifyonly also checks that enough space is there to restore the database

Checks performed by RESTORE VERIFYONLY include:

•That the backup set is complete and all volumes are readable.

•Some header fields of database pages, such as the page ID (as if it were about to write the data).

•Checksum (if present on the media).

•Checking for sufficient space on destination devices.

Please show me the message which you got when verifyonly failed

After Verifyonly fails

I would run a checkdb on my database to check for any inconsistency but point here to note is checkdb does not do consistency check for log file only when recovery on snapshot is run during checkdb log file is used so we 'can' say that it checks for log file as well but not complete like it does for data file. If checkdb comes out clean I would say my database is consistent.

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?

Yes you are correct taking multiple backup does not fixes corruption in log backup but taking multiple log backups does not break log chain. Log files are inked internally with LSN number and taking multiple log backup does not breaks chain if log backups are restored in sequential manner

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

Restore verifyonly(as per BOL)Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. So even if backup set comes as verified clean by Verifyonly its not 100% guaranteed that backup set is consistent only a successful restore of a backup can guarantee that backup set is valid.

I have not see an scenario where verifyonly fails but restores succeeds(unless you succeed with using continue_after_error) it can only happen when verifyonly failed saying there is not enough space to check backup consistency because verifyonly also checks that enough space is there to restore the database

Checks performed by RESTORE VERIFYONLY include:

•That the backup set is complete and all volumes are readable.

•Some header fields of database pages, such as the page ID (as if it were about to write the data).

•Checksum (if present on the media).

•Checking for sufficient space on destination devices.

Please show me the message which you got when verifyonly failed

After Verifyonly fails

I would run a checkdb on my database to check for any inconsistency but point here to note is checkdb does not do consistency check for log file only when recovery on snapshot is run during checkdb log file is used so we 'can' say that it checks for log file as well but not complete like it does for data file. If checkdb comes out clean I would say my database is consistent.

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?

Yes you are correct taking multiple backup does not fixes corruption in log backup but taking multiple log backups does not break log chain. Log files are inked internally with LSN number and taking multiple log backup does not breaks chain if log backups are restored in sequential manner

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

It wont solve corruption issue in any way. Differential backups are means to lower the RTO and perform speedy recovery of database thats it.