What are Backup Checksums and How to Disable Them in SQL Server

In this article, we learn about backup checksums and how to disable them using SSMS

The Checksum property in SQL Server can be put to use to detect what the changes are being made in SQL Server rows or expressions over a period of time. Tracking data and changes in an application like SQL Server can be very challenging and resource intensive at times. To help users deal with this challenge, they have been provided with the checksums property. Once you apply this to any given field or table, it will begin accounting for all the changes being made. And when you add a checksum in the syntax, you will get a binary checksum value that will provide you with the changes incorporated over a period of time. This can thus be considered as a property that is used for tracking the changes being made in SQL Server data.

Backup Checksums and How to Disable Them in SQL Server

Backup Checksums

There are different types of checksums that you can make use of, backup checksums are one of them. Under backup checksums also there are several types that you can opt for using. Depending on the kind of checksums you make use of, you can check for replication, corruption, and updates being made in the database backups.

The major reason why checksums are used during backups is to ensure that the data has not been corrupted. That is one of the greatest areas where checksums help you. Before you move into disabling checksums using SSMS, there are certain crucial factors that you need to know about SQL Server backups and restore.

SQL Server Backups

 The Backup Log and Backup Database permissions in SQL Server are available with fixed server roles like the sysadmin, db_owner, and db_backupoperator by default. The problems regarding the ownership and permission over the physical file of the backup device often interfere with backup operations. Therefore it is imperative for SQL Server to have the ability for reading and writing to the device, thus the account using which the SQL Server application is running should definitely have write permissions.  

SQL Server Restore

If the database you are trying to restore does not even exist, the user will need access to Create Database permission so that they can at opt for executing Restore. If the database is present, the Restore permission will be available by default to members with fixed server roles like sysadmin, dbcreator, and dbo (database owner).

The permission for Restore corrupt sql server database is usually given to roles where the information on membership is readily available at the server all times. And because the membership details for fixed roles are also available when there is no damage to the database and it is easily accessible, that is why even the fixed database roles are not always provided with Restore permissions.

Disabling Backup Checksums using SQL Server Management Studio

To be able to enable or disable backup checksums in SQL Server, using Management Studio, follow the given steps.

  1. Opt for creating a SQL Server database, to start with.
  2. Once this is done, you need to go to the Reliability section found in the Options page, from here select ‘Perform checksum before writing to media’.

  You can also opt for making changes in T – SQL for disabling or enabling backup checksums in SQL Server

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com

Comments are closed.