none
Database Integrity check failed, how to find an un-corrupted backup for recovery

    Question

  • I got database integrity check task that runs weekly. The job ran March 23rd but failed on March 30th. We have identified that there is a corruption in database and now the task is to restore it from backup (with data loss). We have database backup running every-night and I need to know how can I find which is the latest backup that's not corrupted.

    The MSDN documentation says "RESTORE VERIFYONLY" command does not verify whether the structure of the data contained within the backup set is correct. Does it mean the restore command will not able to detect corruption in the database and I just need to restore each of the backs starting from the latest to see if integrity check fails after restore ? OR RESTORE VERIFYONLY will confirm if the database is un-corrupted ?

    Thursday, April 03, 2014 1:14 AM

Answers

  • The MSDN documentation says "RESTORE VERIFYONLY" command does not verify whether the structure of the data contained within the backup set is correct. Does it mean the restore command will not able to detect corruption in the database and I just need to restore each of the backs starting from the latest to see if integrity check fails after restore ? OR RESTORE VERIFYONLY will confirm if the database is un-corrupted ?

    As the documentation suggests, RESTORE VERIFYONLY checks the structure of the backup but not the database itself.  You'll need to restore the backup to check the database consistency.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, April 03, 2014 1:23 AM
  • Agree with Dan,best possible way to know you have consistent backup is to restore it on another server and check it.But IMO Verify only almost checks every aspect of consistency.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, April 03, 2014 8:19 AM

All replies

  • The MSDN documentation says "RESTORE VERIFYONLY" command does not verify whether the structure of the data contained within the backup set is correct. Does it mean the restore command will not able to detect corruption in the database and I just need to restore each of the backs starting from the latest to see if integrity check fails after restore ? OR RESTORE VERIFYONLY will confirm if the database is un-corrupted ?

    As the documentation suggests, RESTORE VERIFYONLY checks the structure of the backup but not the database itself.  You'll need to restore the backup to check the database consistency.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, April 03, 2014 1:23 AM
  • Thanks for the confirmation Dan. I had a feeling that RESTORE VERIFYONLY does not check the integrity. I just ran the command on last night backup and it reported "The backup set on file 1 is valid".

    I will plan to restore each of the backup and check its consistency to identify the latest un-corrupted.

    Thursday, April 03, 2014 1:52 AM
  • Agree with Dan,best possible way to know you have consistent backup is to restore it on another server and check it.But IMO Verify only almost checks every aspect of consistency.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, April 03, 2014 8:19 AM