SQL DB Data Corruption - Insights & Detection

What is “Data Corruption”?

Data corruption is when data becomes unusable, unreadable or in some other way inaccessible to a user or application. It also occurs when a data entity or element or instance loses its base integrity and changes into a structure that is not necessary for the user or the application accessing it. Factors that trigger data corruption, it is often enabled through an external virus or installed within the device. The virus overwrites the primary data, transforms the code or forever destroys it.

Besides viruses, data corruption may also occur as a result of hardware or software failures, errors and environmental catastrophes such as power outages, storms or other disasters.

Now, let us focus on how to detect corruption in the SQL Server database. You can check it in four different ways.

  • Recovery – Page Verify setting – It can be set from SSMS or using a T-SQL script. This option indicates how the page’s health is verified when a page is read or write
    • ALTER DATABASE <DB Name> SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;
  • Backup with Checksum – “Specifies that the backup operation verifies each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup.” This means that SQL Server does page checks during the backup process. If corruption is detected, the backup will fail.
    • BACKUP DATABASE <DB Name> TO DISK='<File_Path>.bak’ WITH CHECKSUM;
  • DBCC CHECKDB – This T-SQL statement checks for “DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECK CATALOG commands do not have to be run separately. When corruptions are detected, this job may succeed with information in the output or fail with an error message.
    • DBCC CHECKDB(DB_Name)

Nimesa software can help in ensuring “Backup Assurance” by enabling customers to do “DR Drills” at regular intervals using the “DB Clone” feature without impacting the production loads.

About Nimesa

Nimesa is enterprise-class Application-Aware data protection, cost management & copy data management solution for applications running on AWS. It uses native AWS capabilities like EBS snapshots capabilities to automatically protect the environment. It provides simple policy-based lifecycle management of snapshots and clones of EC2 instances.

To know more of how to protect your environment using Nimesa refer to the previous blog

Try Nimesa for free