How to Backup WITH CHECKSUM in SQL Server ?

Peter Cole | October 11, 2017 | SQL 2016 Hosting | No Comments

In this tutorial, I will tell you about how to backup with CHECKSUM in SQL Server 2016. When you backup WITH CHECKSUM option is used while taking backup. The backup process can verify each page for checksums & 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.

And here is the code that I used:

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

1 2	backup database sqldba to disk = 'c:\learnsql\sqldba1.bak' with checksum

You can likewise utilize the 3023 trace flag for the check sum choice, when 3023 trace flag is on WITH CHECKSUM alternative will be connected all the reinforcements of course.

DBCC TRACEON(3023)
GO                               
backup database sqldba to disk = 'c:\learnsql\sqldbatraceflag.bak'
DBCC TRACEOFF(3023)
GO

DBCC TRACEON(3023)

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

DBCC TRACEOFF(3023)

We should check if the 3023 trace flag connected the page checksum to above reinforcement summon or not. Execute underneath inquiry for it.

RESTORE HEADERONLY

FROM DISK = 'c:\learnsql\sqldbatraceflag.bak'

GO

RESTORE HEADERONLY

FROM DISK = 'c:\learnsql\sqldbatraceflag.bak'

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

Related Posts

About The Author

Pete