Backup Compression and Checksum

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:

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.

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.

Comments

• Anonymous
  February 05, 2013
  The comment has been removed

• Anonymous
  February 05, 2013
  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?

• Anonymous
  February 05, 2013
  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.

• Anonymous
  February 05, 2013
  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.

• Anonymous
  February 05, 2013
  Any relevant events in Windows Application Log reported from SQLVDI source?

• Anonymous
  February 06, 2013
  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=GlobalRARDEVICE_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>GlobalRARDEVICE_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=GlobalRARDEVICE_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>GlobalRARDEVICE_SQLVDIMemoryName_0</Data>  </EventData> </Event>

• Anonymous
  February 06, 2013
  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?

• Anonymous
  February 06, 2013
  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

• Anonymous
  February 06, 2013
  The comment has been removed

• Anonymous
  February 06, 2013
  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.