MSSQL backup with checksum

Question

If my database have checksum enable
when my database backup with checksum option .
so it will only calculate the checksum only for the whole bak file ?

Answer 1

so it will only calculate the checksum only for the whole bak file ?

Yes, official document clearly states that it is for entire backup file. Checksum when used in backup command, will test the page checksums that exist on the data file pages being backed up. If a bad page checksum is found, the backup process will stop (by default). Please note checksum is not replacement of dbcc checkdb, not all corruption can be find out using checksum.

EDIT: Further information added after follow up question

how does it verify checksum of EACH page??

Each page is read from disk when backup operation starts and checksum is added to each page and at last checksum for whole backup file is calculated and matched with sum of checksum of each page. Paul randal writes in his blog

If you have page checksums turned on, you should always use the WITH CHECKSUM option when taking backups. This will cause the page checksums to be checked as they’re read into the backup. If a bad page checksum is found, the backup will stop and print a message identifying the bad page. Using the WITH CHECKSUM option on a backup will also generate a checksum over the entire backup stream and store it in the backup. This means we can detect a damaged backup by recalculating the checksum and comparing it against that stored in the backup – in much the same way that page checksums work.

So it generate a checksum again for EACH page and compare the CHECKSUM of the page?

Backup checksum will not generate checksum but will verify the checksum already in page header. For this to happen database must have page checksum enabled and pages must be read and written back in memory for them to be enabled. Please also see backup-checksum-default-option-in-sql-server-2014

So there is no checksum generated but its basically matched

Answer 2

Hi @sakuraime ,

> so it will only calculate the checksum only for the whole bak file ?

Yes. Full agree with Shashank. It is for entire backup file.

If you don’t tell SQL to restore with checksum, regardless of if you did checksums when you backed it up, it will just skip the checksums. Refer to this blog Backup and Restore Databases with CHECKSUM and VERIFYONLY.

The backup with checksum completes successfully that do not mean there are no corruptions. Using WITH CHECKSUM is not testing the I/O subsystem integrity of the entire data file – only those pages with page checksums. Please refer to the blog Does using WITH CHECKSUM ensure a successful backup?

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.