Database Backup WITH CHECKSUM


Backup WITH CHECKSUM

When WITH CHECKSUM option is used while taking backup, the backup process will verify each page for checksums and torn page. In case bad page checksum found, the backup will stop. Using backup checksums may affect workload and backup throughput.

If you want to continue the backup, you have to write CONTINUE_AFTER_ERROR in backup WITH clause.
In case backup completes successfully it means no bad checksums.

Example

backup database sqldba to disk = 'c:\jspace\sqldba1.bak'
with checksum

You can also use the 3023 trace flag for the check sum option, when 3023 trace flag is on WITH CHECKSUM option will be applied all the backups by default.

DBCC TRACEON(3023)
GO                              

backup database sqldba to disk = 'c:\jspace\sqldbatraceflag.bak'

DBCC TRACEOFF(3023)
GO                              

Let’s check if the 3023 trace flag applied the page checksum to above backup command or not. Execute below query for it.

RESTORE HEADERONLY
FROM DISK = 'c:\jspace\sqldbatraceflag.bak'
GO  

HasBackupCheckSums value is 1, it means that WITH CHECKSUM option applied to backup using 3023 trace flag.
checksum

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s