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 ?

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 ?

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
my point is how the backup up (with checksum) verify the checksum of each page is correct??
you mention ":
"Each page is read from disk when backup operation starts and checksum is added to each page ". Which means the checksum of each page was calculated again ? to compare the checksum value which read from the page from the disk ??
@sakuraime So when you enable page checksum on database and pages are read in memory and written back to disk checksum is stamped on header of the page. During the backup operation this page is read and checksum is calculated again plus for all pages checksum is summed up and total checksum is also calculated by backup for whole backup file this value is also matched. Hope this is clear now
thanks for your information .
If the database checksum set to None, then the backup with checksum will only generate the checksum of the backup only ?
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.
Hi @sakuraime,
Any update for this thread? Did the reply could help you? If the response helped, do "Accept Answer".
Best regards,
Cathy
how does it verify checksum of EACH page??
So it generate a checksum again for EACH page and compare the CHECKSUM of the page?
@sakuraime I have added answer to your follow up question in my original answer, please check
4 people are following this question.