the original database is fine, but restoring database has consistency errors

  • i1888

    SSCommitted

    Points: 1810

    Hello,

    We have a client in another country, I have asked them to send me the full backup in a usb, and upload ongoing log files via SFTP, something weird is, every time I restored the full backup, the log restore will fail at some point, I asked them to resend new full backup, after 3 times, I think there is something wrong rather than log file.

    So I restored the full backup, bring it online, run dbcc checkdb(dbname), then I got consistency errors in database, but from customer side, dbcc checkdb doesn't return any errors, there is no faulty hardwares in client server.

    1) All log files are valid

    2) Database file is valid

    Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

    The path specified by "D:\Data\XXXYYY_Data.mdf" is not in a valid directory.

    Directory lookup for the file "E:\Log\XXXYYY_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).

    The backup set on file 1 is valid.

    3)

    DBCC CHECKDB WITH NO_INFOMSGS;

    GO

    CHECKDB found 0 allocation errors and 12 consistency errors in table 'XXX' (object ID 822747596).

    CHECKDB found 0 allocation errors and 19 consistency errors in database 'XXXYYY'.

    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (XXXYYY).

    4) SELECT * FROM msdb..suspect_pages

    43 1 3202572

    2 10 2015-05-15 12:07:30.907

    43 1 3202573

    2 10 2015-05-15 12:07:30.910

    43 1 1050913

    2 10 2015-05-15 12:13:57.347

    43 1 4827952

    2 10 2015-05-15 12:14:04.203

    client is running on

    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

    my sql server is running on

    Microsoft SQL Server 2014 - 12.0.2495.0 (X64)

    Mar 31 2015 09:47:37

    Copyright (c) Microsoft Corporation

    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

  • Eirikur Eiriksson

    SSC Guru

    Points: 182508

    i1888 (5/14/2015)


    Hello,

    We have a client in another country, I have asked them to send me the full backup in a usb, and upload ongoing log files via SFTP, something weird is, every time I restored the full backup, the log restore will fail at some point, I asked them to resend new full backup, after 3 times, I think there is something wrong rather than log file.

    So I restored the full backup, bring it online, run dbcc checkdb(dbname), then I got consistency errors in database, but from customer side, dbcc checkdb doesn't return any errors, there is no faulty hardwares in client server.

    1) All log files are valid

    2) Database file is valid

    Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

    The path specified by "D:\Data\XXXYYY_Data.mdf" is not in a valid directory.

    Directory lookup for the file "E:\Log\XXXYYY_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).

    The backup set on file 1 is valid.

    3)

    DBCC CHECKDB WITH NO_INFOMSGS;

    GO

    CHECKDB found 0 allocation errors and 12 consistency errors in table 'XXX' (object ID 822747596).

    CHECKDB found 0 allocation errors and 19 consistency errors in database 'XXXYYY'.

    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (XXXYYY).

    4) SELECT * FROM msdb..suspect_pages

    43 1 3202572

    2 10 2015-05-15 12:07:30.907

    43 1 3202573

    2 10 2015-05-15 12:07:30.910

    43 1 1050913

    2 10 2015-05-15 12:13:57.347

    43 1 4827952

    2 10 2015-05-15 12:14:04.203

    client is running on

    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

    my sql server is running on

    Microsoft SQL Server 2014 - 12.0.2495.0 (X64)

    Mar 31 2015 09:47:37

    Copyright (c) Microsoft Corporation

    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

    Quick thought, if the DBCC CHECKDB returns no errors on the client database then it is fine and the corruption is happening down the line. Ask the client to restore the full backup from the media they are sending you and run DBCC CHECKDB on the restore. If that comes out clean then the problem is almost certainly your hardware.

    😎

    Also make certain that DBCC is run with

    DBCC CheckDB ('[database name]') WITH NO_INFOMSGS, ALL_ERRORMSGS;

  • cunningham

    SSCarpal Tunnel

    Points: 4595

    in addition to the above, do you have a sql 2008r2 server you can restore the backup to to rule out version difference being the problem?

  • Grant Fritchey

    SSC Guru

    Points: 396692

    It is possible for the backup itself to become corrupted. I'm with Eirikur, test it on the client side first to see if they get the same issue.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • SQL Guy 1

    SSCoach

    Points: 15732

    i1888 (5/14/2015)


    So I restored the full backup, ...

    Attempting to restore this backup ...

    So did you actually restore it, or just attempted to restore?

    Did you use MOVE clauses to place the files in your drives?

  • i1888

    SSCommitted

    Points: 1810

    Hi

    I did build a SQLServer2008R2 in my virtual machine to restore the database, same error...

  • i1888

    SSCommitted

    Points: 1810

    Hi

    When do real full backup restore I will set move clause, when restore verifyonly, I just leave as it is.

  • i1888

    SSCommitted

    Points: 1810

    Hi All,

    I used SFTP instead of FTP to transfer the file, I can verify the files on client server and my server are identical(as checksum is same), but I cannot restore database on my server ...

    Client Server:

    1. Verify backup

    restore verifyonly from disk = '\\BACKUP-PC\BACKUP\XXXYYY.bak'

    The backup set on file 1 is valid.

    2. Checksum

    C:\temp>fciv.exe C:\backup\XXXYYY.bak

    //

    // File Checksum Integrity Verifier version 2.05.

    //

    ccd6073922f7cbaba50fe9ed038bcd95 C:\backup\XXXYYY.bak

    C:\temp>

    My server- After SFTP transferring to my server:

    1.restore verifyonly from DISK = N'D:\Backups\XXXYYY.bak'

    Msg 3203, Level 16, State 1, Line 7

    Read on "D:\Backups\XXXYYY.bak" failed: 13(The data is invalid.)

    Msg 3013, Level 16, State 1, Line 7

    VERIFY DATABASE is terminating abnormally.

    2. Checksum

    D:\Tools>fciv.exe d:\Backups\XXXYYY.bak

    //

    // File Checksum Integrity Verifier version 2.05.

    //

    ccd6073922f7cbaba50fe9ed038bcd95 d:\backups\XXXYYY.bak

    D:\Tools>

    The client backup the database to another server "Backup-PC", I am not sure if this can be the cause?

    I will see if they have space to do a restore on their environment.

  • i1888

    SSCommitted

    Points: 1810

    I am doing the test at the moment.

  • i1888

    SSCommitted

    Points: 1810

    I managed to made a backup locally rather than backup to another server, the backup looks good, I can restore to the client sql server, and also able to restore it to my test server.

    There must be something wrong of their network, but anyway, it is good to know the database itself is healthy.

    If the backup was made via network to another server, one thing worry about me is the restoreverify command will think the backup is valid, but actually not...

    Anyway, thanks all for you help.

  • Lynn Pettis

    SSC Guru

    Points: 442359

    i1888 (5/20/2015)


    I managed to made a backup locally rather than backup to another server, the backup looks good, I can restore to the client sql server, and also able to restore it to my test server.

    There must be something wrong of their network, but anyway, it is good to know the database itself is healthy.

    If the backup was made via network to another server, one thing worry about me is the restoreverify command will think the backup is valid, but actually not...

    Anyway, thanks all for you help.

    Keep this in mine:

    Backups are worthless, Restores are priceless.

    The only true validation of a backup file is to restore the database from the backup. Can be to the same server under a different name or to a different server with the original name.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply