none
database integrity chechk failed after restore RRS feed

  • Question

  • Good mourning, colleagues.

    Here's the problem: I have a protection group containing SQL databases, and tape backup, scheduled to performed once a week.

    While recovering some bases from tape we found out that restored bases turned out to fail the intergrity check, and all that backups are created during the same month. all the other recovery points on disk and tape pass all the tests successfully.

    No DPM events, related to these recovery pints on tape were found - so DPM consideres these backups to be valid.

    So - is there any way to check the backed up SQL databases for integrity, or some other way to avoid such a fail with it?

    Tuesday, March 10, 2015 9:12 AM

Answers

  • Hi,

    There are two aspects to this.

    A) Are the disk based recovery points good.
    B) Are the tape based recovery points made from the disk based recovery points good.

    As with any backup product, the backups are only good if the restores are good, so periodic test restores should be done on a regular basis.  This is backup operator 101.

    To check  backup integrity - you can perform these two steps to help discover and catch inconsistencies quickly.

    A) For DISK based recovery points - make a new recovery point with integrity check using the following, it still applies to DPM 2012.

    New-RecoveryPoint
    http://technet.microsoft.com/en-us/library/cc627335.aspx

    Syntax

    New-RecoveryPoint –Datasource name of data source -Disk -BackupType expressfull -WithDataIntegrityCheck

    Example – Integrity Check
    The following sequence of commands allows you to create a new recovery point and perform an integrity check on it before storing it to the variable recoverypt.

    $dpmname = (&HOSTNAME)
    $pg = Get-ProtectionGroup -DPMServerName $dpmname
    $pg
    $ds = Get-Datasource -ProtectionGroup $pg[X]         #(WHERE X IS A ZERO BASED INDEX FROM THE OUTPUT OF $PG)
    $ds
    $recoverypt = New-RecoveryPoint -Datasource $ds[Y] -Disk -BackupType expressfull -WithDataIntegrityCheck  #(WHERE Y IS A ZERO BASED INDEX FROM THE OUTOUT OF $DS)

    If it finds any mismatch, it fixes the replica for the same and it raises the alert that corruption has been detected.

    -OR for volumes use -

    $dpmname = (&HOSTNAME)
    $pg = Get-ProtectionGroup -DPMServerName $dpmname
    $pg
    $ds = Get-Datasource -ProtectionGroup $pg[X]         #(WHERE X IS A ZERO BASED INDEX FROM THE OUTPUT OF $PG)
    $ds
    $recoverypt = New-RecoveryPoint $ds[y] -Disk -DiskrecoveryPointOption WithSynchronize -WithDataIntegrityCheck  #(WHERE Y IS A ZERO BASED INDEX FROM THE OUTOUT OF $DS)


    If it finds any mismatch, it fixes the replica for the same and it raises the alert that corruption has been detected.

    B) For tape based recovery points - be sure to enable the checkbox "Check backup for data integrity" on the Select library and tape details page.  If the verify fails then the backup is not reliable.

    To check backups after the fact, you can verify the recovery points using Powershell.


    Test-DPMTapeData
    http://technet.microsoft.com/en-nz/library/ff631948(en-us).aspx


    PS C:\Program Files\Microsoft DPM\DPM\bin> get-help Test-DPMTapeData -examples

    NAME
        Test-DPMTapeData

    SYNOPSIS
        Verifies the data set for a recovery point.

        EXAMPLE 1

        $pg = Get-ProtectionGroup -DPMServerName (&hostname)
        $ds = Get-Datasource -ProtectionGroup $pg[x]
        $rp = Get-RecoveryPoint -Datasource $ds[x]
        $rsl = Get-RecoveryPointLocation -RecoveryPoint $rp[1]
        Test-DPMTapeData -RecoveryPoint $rp[1] -RecoveryPointLocation $rsl


        This command verifies the recovery point at the specified location.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.


    Tuesday, March 10, 2015 5:12 PM
    Moderator