Are CHECKSUMs being performed with a Compressed Backup?

2013-12-30 Wayne Sheffield 3 comments

Now, why in the world would I be asking a question like this?
Well, in BOL (BACKUP) is this little blurb:

CHECKSUM
Specifies that the backup operation will verify each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup. This is the default behavior for a compressed backup.

Using backup checksums may affect workload and backup throughput.: (Emphasis mine)

However, when performing a compressed backup and examining the results in msdb.dbo.backupset, the has_backup_checksums column is not set to 1. Let’s run through a quick example to see what I’m talking about.

First, let’s create a database and a table, and populate it with a million rows:

USE master;
GO
-- drop the database if it already exists
IF DB_ID('BackupTest') IS NOT NULL DROP DATABASE BackupTest;
GO
-- make a database. make a table. populate the table.
CREATE DATABASE BackupTest;
GO
USE BackupTest;
GO
CREATE TABLE dbo.Tally (
    N INTEGER CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED 
    );
WITH Tens    (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
     Hundreds(N) AS (SELECT 1 FROM Tens t1, Tens t2),
     Millions(N) AS (SELECT 1 FROM Hundreds t1, Hundreds t2, Hundreds t3),
     Tally   (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions)
INSERT INTO dbo.Tally (N)
SELECT N FROM Tally;
GO

USE master;

-- drop the database if it already exists

IF DB_ID('BackupTest') IS NOT NULL DROP DATABASE BackupTest;

-- make a database. make a table. populate the table.

CREATE DATABASE BackupTest;

USE BackupTest;

CREATE TABLE dbo.Tally (

N INTEGER CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED

);

WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

Hundreds(N) AS (SELECT 1 FROM Tens t1, Tens t2),

Millions(N) AS (SELECT 1 FROM Hundreds t1, Hundreds t2, Hundreds t3),

Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions)

INSERT INTO dbo.Tally (N)

SELECT N FROM Tally;

Now, let’s perform various full database backups on this database.

USE master;
GO
-- ensure that the backup directory exists
EXECUTE xp_create_subdir 'C:\BackupTest';

-- plain full backup, no options
BACKUP DATABASE BackupTest 
TO DISK = 'C:\BackupTest\BackupTest1_FULL_NoOptions.bak';
GO
-- full backup with checksums
BACKUP DATABASE BackupTest
TO DISK = 'C:\BackupTest\BackupTest2_FULL_CHECKSUM.bak'
WITH CHECKSUM;
GO
-- compressed backup
BACKUP DATABASE BackupTest
TO DISK = 'C:\BackupTest\BackupTest3_FULL_COMPRESSION.bak'
WITH COMPRESSION;
GO
-- compressed backup, no checksums
BACKUP DATABASE BackupTest
TO DISK = 'C:\BackupTest\BackupTest4_FULL_COMPRESSION_NOCHECKSUM.bak'
WITH COMPRESSION, NO_CHECKSUM;
GO
-- compressed backup, checksums
BACKUP DATABASE BackupTest
TO DISK = 'C:\BackupTest\BackupTest5_FULL_COMPRESSION_CHECKSUM.bak'
WITH COMPRESSION, CHECKSUM;
GO

USE master;

-- ensure that the backup directory exists

EXECUTE xp_create_subdir 'C:\BackupTest';

-- plain full backup, no options

BACKUP DATABASE BackupTest

TO DISK = 'C:\BackupTest\BackupTest1_FULL_NoOptions.bak';

-- full backup with checksums

BACKUP DATABASE BackupTest

TO DISK = 'C:\BackupTest\BackupTest2_FULL_CHECKSUM.bak'

WITH CHECKSUM;

-- compressed backup

BACKUP DATABASE BackupTest

TO DISK = 'C:\BackupTest\BackupTest3_FULL_COMPRESSION.bak'

WITH COMPRESSION;

-- compressed backup, no checksums

BACKUP DATABASE BackupTest

TO DISK = 'C:\BackupTest\BackupTest4_FULL_COMPRESSION_NOCHECKSUM.bak'

WITH COMPRESSION, NO_CHECKSUM;

-- compressed backup, checksums

BACKUP DATABASE BackupTest

TO DISK = 'C:\BackupTest\BackupTest5_FULL_COMPRESSION_CHECKSUM.bak'

WITH COMPRESSION, CHECKSUM;

Now let’s looks at the results of these backups as stored in msdb:

-- show the backups that were performed along with relevant information
SELECT  [database name] = database_name, 
        [backup start date] = backup_start_date,
        [has backup checksums] = has_backup_checksums,
        [backup size] = backup_size,
        [compressed backup size] = compressed_backup_size 
FROM    msdb.dbo.backupset 
WHERE   database_name = 'BackupTest'
AND     backup_start_date &gt; DATEADD(DAY, DATEDIFF(DAY, '2000-01-01', GETDATE()), '2000-01-01')
;

-- show the backups that were performed along with relevant information

SELECT [database name] = database_name,

[backup start date] = backup_start_date,

[has backup checksums] = has_backup_checksums,

[backup size] = backup_size,

[compressed backup size] = compressed_backup_size

FROM msdb.dbo.backupset

WHERE database_name = 'BackupTest'

AND backup_start_date > DATEADD(DAY, DATEDIFF(DAY, '2000-01-01', GETDATE()), '2000-01-01')

;

For the backups I just performed, this shows (I’ve manually added the last column):

database name	backup start date	has backup checksums	backup size	compressed backup size	options used
BackupTest	2013-12-18 14:34:50.000	0	17,015,808	17,015,808	none
BackupTest	2013-12-18 14:34:50.000	1	17,015,808	17,015,808	CHECKSUM
BackupTest	2013-12-18 14:34:50.000	0	17,015,808	2,667,989	COMPRESSION
BackupTest	2013-12-18 14:34:50.000	0	17,015,808	2,667,990	COMPRESSION NO_CHECKSUM
BackupTest	2013-12-18 14:34:50.000	1	17,015,808	2,518,869	COMPRESSION CHECKSUM

Let’s look at what the actual backup files show…

-- show the backup file headers
RESTORE HEADERONLY FROM DISK = 'C:\BackupTest\BackupTest1_FULL_NoOptions.bak';
RESTORE HEADERONLY FROM DISK = 'C:\BackupTest\BackupTest2_FULL_CHECKSUM.bak';
RESTORE HEADERONLY FROM DISK = 'C:\BackupTest\BackupTest3_FULL_COMPRESSION.bak';
RESTORE HEADERONLY FROM DISK = 'C:\BackupTest\BackupTest4_FULL_COMPRESSION_NOCHECKSUM.bak';
RESTORE HEADERONLY FROM DISK = 'C:\BackupTest\BackupTest5_FULL_COMPRESSION_CHECKSUM.bak';

-- show the backup file headers

RESTORE HEADERONLY FROM DISK = 'C:\BackupTest\BackupTest1_FULL_NoOptions.bak';

RESTORE HEADERONLY FROM DISK = 'C:\BackupTest\BackupTest2_FULL_CHECKSUM.bak';

RESTORE HEADERONLY FROM DISK = 'C:\BackupTest\BackupTest3_FULL_COMPRESSION.bak';

RESTORE HEADERONLY FROM DISK = 'C:\BackupTest\BackupTest4_FULL_COMPRESSION_NOCHECKSUM.bak';

RESTORE HEADERONLY FROM DISK = 'C:\BackupTest\BackupTest5_FULL_COMPRESSION_CHECKSUM.bak';

Which returns the same results as the query against the msdb.dbo.backupset table.

So, where does this leave us? Are checksums being performed with a compressed backup? By looking at only the has_backup_checksums column, then it appears the answer is no. Even when you also look at the compressed backup size, any compressed backup without the checksum option gives file sizes of the same relative size (I show a one-byte difference). So, we appear to have a discrepancy between the documented behavior in BOL, and what is actually being observed.

So the real question becomes: Does performing a compressed backup provide the same usefulness as when specifying the checksum option? As it turns out, after I started writing this blog post (and before I finished it), Brent Ozar had the same question. Doing something that I hadn’t considered, he pulled out his hex editor and… well, read about it here. The quick summary is: if you don’t specify the checksum option, then page checksums / torn pages aren’t being checked for. Which means that BOL is wrong. (Thanks Brent!)

It is my opinion that when the compressed backup is occurring, it calculates an internal checksum for the data being backed up (a calculation based upon the data being backed up, NOT verifying the page checksum values previously calculated or checking for torn pages like the checksum option for the backup command does). I think that many years ago, back when backup compression was being added to SQL 2008, the BOL writer saw “checksum is calculated during a compressed backup”, and wrongfully wrote up that the checksum option is the default for a compressed backup. Again, this is just my opinion, and I have no facts to back this up.

Since it is a good practice to utilize the checksum option when doing backups (to ensure that there isn’t any corruption from the IO subsystem), you should know that you cannot perform this from a maintenance plan – that option isn’t there. I was really hoping that the compressed backups did do the checksums, as it would be easy to implement for all of the clients that still use maintenance plans (since making a compressed backup is an option in the maintenance plan). So you will need to use a solution based upon executing the T-SQL statement to use the checksum option (such as Ola Hallengren’s free solution). This is just another reason why using a script-based solution is better than using a maintenance plan.

If you made it this far, I hope that you will visit this connect item (Backup COMPRESSION enables CHECKSUM?) to vote on getting this fixed.

SQL none

#1 | Written by Svetlana Golovko about 11 years ago. Reply

I think that it takes a name (Brent Ozar, for example :)) to bring attention to issues (even though this one seems like a small one). It’s been more than a year since I submitted this on Microsoft Connect, but only now it became more public, I would say.
Thanks for bringing this up.
#2 | Written by Brad about 11 years ago. Reply

Any thoughts about the COMPRESSED, CHECKSUM backup being 149,121 bytes smaller than the other COMPRESSED backups?

Or is this just magic?

Your in anal-retentive-DBA-ish-ness,

Brad
- #3 | Written by Wayne Sheffield about 11 years ago. Reply
  
  My speculation is that compressed backups normally calculate checksums, but when using the checksum option and the page already has a checksum, it is smart enough to realize that another one isn’t necessary, which ends up saving some space. However, I honestly have no idea, so until I do hear back why it would be smaller, attributing this to magic works fine for me.

Wayne Sheffield

Recent Posts

Top Posts

Archives

Categories

Blogroll

Are CHECKSUMs being performed with a Compressed Backup?

Like this:

Related

Leave a Reply to let me know how you liked this postCancel reply

Wayne Sheffield

Recent Posts

Top Posts

Archives

Categories

Tags

Blogroll

Are CHECKSUMs being performed with a Compressed Backup?

Share this:

Like this:

Related

Leave a Reply to let me know how you liked this postCancel reply