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.
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 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
Closing device. SqlState:HY000 NativeError:3624
A system assertion check has failed...
Waiting for restore thread done.
Expression: m_pDecodeSplitInput->GetAvailableFreeSize () >= lengthInNext
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?
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
Date: 06.02.2013 13:55:19
Event ID: 1
Task Category: None
SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=6300. Thread=6204. Client. Instance=. VD=Global\RARDEVICE_SQLVDIMemoryName_0.
<Provider Name="SQLVDI" />
<TimeCreated SystemTime="2013-02-06T09:55:19.000000000Z" />
<Data>Client initiates abort</Data>
SQLVDI: Loc=TriggerAbort. Desc=invoked. ErrorCode=(0). Process=2276. Thread=2716. Server. Instance=SQL2008. VD=Global\RARDEVICE_SQLVDIMemoryName_0.
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
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.