Backup Compression and Checksum

Backup Compression and Checksum

Rate This
  • Comments 10

Notice that when I wrote this repro and captured the screenshots available below, I didn’t think they will be rendered in this blog. Unfortunately, you will have to click on each image to be able to read the descriptions of every relevant chunk of data highlighted in the raw data stream.

A while ago Paul asked me about this topic and the confusion it tends to cause to many people. Since I received this question again recently from another PFE at Microsoft, I decided to share this info through my blog so that more people can benefit from it.

Backup compression does a checksum over the whole backup (but doesn't test page checksums). So where's the checksum over the whole backup stored? And how can it be tested? Or is BOL incorrect?

And this is what I found:

The checksums calculated to satisfy the presence of the CHECKSUM clause are enabled on a per backupset basis and are persisted in this way:

clip_image002

clip_image002[6]

For the computation of that checksum, page checksums (when present) are leveraged so that the operation completes faster. If for a particular page, its header reveals that page checksum is not calculated, then it will be computed by the backup operation, but won’t modify the page header at all.

On the other hand, when we use backup compression that is only possible if the mediaset is formatted to support compression. Meaning it affects everything stored in that given mediaset. The checksums used in compression are calculated differently, over a different data set and also stored in a different way.

clip_image002[8]

With that explained, next question was: So how can the checksum in a compressed backup (not using WITH CHECKSUM) actually be checked without restoring?

And the answer is that if you want to check the checksums created for the compressed blocks, you just have to issue a RESTORE VERIFYONLY and that will check all the checksums in any compressed block from the beginning of the mediaset to the end of the backupset being verified.

Leave a Comment
  • Please add 7 and 8 and type the answer here:
  • Post
  • We use custom-made VDI backup-restore tool from RAR-archive (vdc 0.9.4 - restoring MS SQL database from RAR archive - test version), it works with versions of SQL Server from 2000 to 2008R2, but not with compressed backups from 2008 and 2008R2.

    SQL Server throws a series of errors on RESTORE VERIFYONLY:

    spid53 Error: 17066, Severity: 16, State: 1.

    spid53 SQL Server Assertion: File: <readEncoded.cpp>, line=2699 Failed Assertion = 'm_pDecodeSplitInput->GetAvailableFreeSize () >= lengthInNext'. This error may be timing-related...

    spid53 Error: 3624, Severity: 20, State: 1.

    spid53 A system assertion check has failed...

    spid53 Error: 17066, Severity: 16, State: 1.

    spid53 SQL Server Assertion: File: <bckbuf.cpp>, line=281 Failed Assertion = 'GetAvailableFreeSize () >= size'. This error may be timing-related...

    spid53 Error: 17066, Severity: 16, State: 1.

    spid53 SQL Server Assertion: File: <readEncoded.cpp>, line=2699 Failed Assertion = 'm_pDecodeSplitInput->GetAvailableFreeSize () >= lengthInNext'. This error may be timing-related...

    spid53 Error: 17066, Severity: 16, State: 1.

    spid53 SQL Server Assertion: File: <bckbuf.cpp>, line=281 Failed Assertion = 'GetAvailableFreeSize () >= size'. This error may be timing-related...

    spid53 Error: 17066, Severity: 16, State: 1.

    spid53 SQL Server Assertion: File: <readEncoded.cpp>, line=2699 Failed Assertion = 'm_pDecodeSplitInput->GetAvailableFreeSize () >= lengthInNext'. This error may be timing-related...

    spid53 Error: 17066, Severity: 16, State: 1.

    spid53 SQL Server Assertion: File: <bckbuf.cpp>, line=281 Failed Assertion = 'GetAvailableFreeSize () >= size'. This error may be timing-related...

    Backup Error: 18210, Severity: 16, State: 1.

    Backup BackupIoRequest::ReportIoError: read failure on backup device 'RARDEVICE'. Operating system error 13(failed to retrieve text for this error. Reason: 15105).

    spid53 Internal I/O request 0x000000015A6ACDA0: Op: Read, pBuffer: 0x0000000000000000, Size: 65536, Position: 1251328, SOS: Internal: 0x0, InternalHigh: 0x0, Offset: 0x0, OffsetHigh: 0x0, m_buf: 0x0000000000000000, m_len: 65536, m_actualBytes: 0, m_errcode: 13, BackupFile: RARDEVICE

    The error returned is:

    Unexpected termination: -2139684860

    PerformTransfer:Done

    Closing device. SqlState:HY000 NativeError:3624

    A system assertion check has failed...

    Waiting for restore thread done.

    Location: readEncoded.cpp:2699

    Expression: m_pDecodeSplitInput->GetAvailableFreeSize () >= lengthInNext

    SPID: 53

    Process ID: 2276

    Restore Thread Execution  - failed

    SQL Server @@version is:

    Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64)

    Aug 22 2012 19:25:47

    Copyright (c) 1988-2008 Microsoft Corporation

    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    Could you please shed some light on this?

    Best regards,

    Ilya

  • Does it only happen to fail when you specify the VERIFYONLY clause but it restores from the same backupset successfully when you just remove the VERIFYONLY clause? Is that the case? Can you reproduce this behavior regardless of what was the original database (say master, model, different user DBs) whose compressed backup stream you are attempting to validate?

  • It happens on RESTORE HEADERONLY, VERIFYONLY and DATABASE, yet works on LABELONLY and FILELISTONLY.

    Yep, it is easily reproduced with master/model and empty databases compressed backups. I'm not the author of this tool, but I have its code (Object Pascal with translated headers vdi.h) and permission to changes, I can compile it and try. If only I knew what's wrong or what to change.

    Microsoft SQL Server Backup Simulator v2 validates and simulates VDI nicely on the server.

  • There is also one more problem with the tool. RESTORE HEADERONLY (for not-compressed backups) takes roughly 4 times longer to complete than RESTORE VERIFYONLY or RESTORE DATABASE. Running RESTORE HEADERONLY on bare .bak file completes immediately.

  • Any relevant events in Windows Application Log reported from SQLVDI source?

  • Yep, there are two from SQLVDI:

    Log Name:      Application

    Source:        SQLVDI

    Date:          06.02.2013 13:55:19

    Event ID:      1

    Task Category: None

    Level:         Error

    Keywords:      Classic

    User:          N/A

    Computer:      PDC-SERVER.LVS.Server

    Description:

    SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=6300. Thread=6204. Client. Instance=. VD=Global\RARDEVICE_SQLVDIMemoryName_0.

    Event Xml:

    <Event xmlns="schemas.microsoft.com/.../event">

     <System>

       <Provider Name="SQLVDI" />

       <EventID Qualifiers="0">1</EventID>

       <Level>2</Level>

       <Task>0</Task>

       <Keywords>0x80000000000000</Keywords>

       <TimeCreated SystemTime="2013-02-06T09:55:19.000000000Z" />

       <EventRecordID>265646</EventRecordID>

       <Channel>Application</Channel>

       <Computer>PDC-SERVER.LVS.Server</Computer>

       <Security />

     </System>

     <EventData>

       <Data>SignalAbort</Data>

       <Data>Client initiates abort</Data>

       <Data>0</Data>

       <Data>

       </Data>

       <Data>6300</Data>

       <Data>6204</Data>

       <Data>Client</Data>

       <Data>

       </Data>

       <Data>Global\RARDEVICE_SQLVDIMemoryName_0</Data>

     </EventData>

    </Event>

    Log Name:      Application

    Source:        SQLVDI

    Date:          06.02.2013 13:55:19

    Event ID:      1

    Task Category: None

    Level:         Error

    Keywords:      Classic

    User:          N/A

    Computer:      PDC-SERVER.LVS.Server

    Description:

    SQLVDI: Loc=TriggerAbort. Desc=invoked. ErrorCode=(0). Process=2276. Thread=2716. Server. Instance=SQL2008. VD=Global\RARDEVICE_SQLVDIMemoryName_0.

    Event Xml:

    <Event xmlns="schemas.microsoft.com/.../event">

     <System>

       <Provider Name="SQLVDI" />

       <EventID Qualifiers="0">1</EventID>

       <Level>2</Level>

       <Task>0</Task>

       <Keywords>0x80000000000000</Keywords>

       <TimeCreated SystemTime="2013-02-06T09:55:19.000000000Z" />

       <EventRecordID>265645</EventRecordID>

       <Channel>Application</Channel>

       <Computer>PDC-SERVER.LVS.Server</Computer>

       <Security />

     </System>

     <EventData>

       <Data>TriggerAbort</Data>

       <Data>invoked</Data>

       <Data>0</Data>

       <Data>

       </Data>

       <Data>2276</Data>

       <Data>2716</Data>

       <Data>Server</Data>

       <Data>SQL2008</Data>

       <Data>Global\RARDEVICE_SQLVDIMemoryName_0</Data>

     </EventData>

    </Event>

  • Would you, by any chance, have any security product installed on your machine, like the ones mentioned in support.microsoft.com/.../2033238, that could be injecting code into SQL's or your VDI client's VAS to run Buffer Overrun detection algorithms or similar protection mechanisms?

  • Neither McAfee, nor Sophos. There is local Symantec Endpoint Protection 12.1.2 installed featuring AV service only which has no detours I know about. I've also added to its exclusion list sqlservr.exe as advised in support.microsoft.com/.../309422 just today by chance. I can see no 3rd party DLLs other than of Microsoft in sqlservr.exe process in Process Explorer.

    VDC tool only has UNRAR.DLL and its stack of the most active thread is like

    ntoskrnl.exe!KeWaitForMultipleObjects+0xc0a

    ntoskrnl.exe!NtCreateFile+0x3a4

    ntoskrnl.exe!NtReadFile+0x633

    ntoskrnl.exe!KeSynchronizeExecution+0x3a23

    unrar.dll!RAROpenArchive+0xde72

    unrar.dll!RAROpenArchive+0xdf4f

    vdc.exe+0x2c07

    unrar.dll!RAROpenArchive+0x10e88

    unrar.dll!RAROpenArchive+0x124af

    unrar.dll!RAROpenArchive+0x12a6e

    unrar.dll!RAROpenArchive+0x3f1a

  • That stack of course during normal execution when not dealing compressed backup stream. It fails fast otherwise.

  • Ilya, could you please send me an email to I A L O N S O at M I C R O S O F T dot C O M, so that we follow this up via email? Thanks.

Page 1 of 1 (10 items)