For law firms with premises based systems like Time Matters you may run into situations where the database has not been maintained and has become inconsistent.  This can prevent your ability to create a backup from which you can migrate the customer to a new platform.  This article provides information on resolving I/O consistency errors in a SQL Server database.

WARNING!  Some of these steps can cause data loss. 

This help article is offered in good faith to help you resolve these errors.  We offer no warranty or assurances: follow these instructions at your own risk.  If you choose to proceed with the procedures outlined, do so with caution and the knowledge that data loss is possible.

SQL Server detected a logical consistency-based I/O error: 
incorrect checksum (expected: 0xe0da1e39; actual: 0xd3de2d29). 
It occurred during a read of page (1:564637) in database ID 5 
at offset 0x00000113b3a000 in file '{path to file}'. Additional
messages in the SQL Server error log or operating system error 
log may provide more detail. This is a severe error condition 
that threatens database integrity and must be corrected 
immediately. Complete a full database consistency check 
(DBCC CHECKDB). This error can be caused by many factors; 
for more information, see SQL Server Books Online.

There are several potential causes to this type of error.

• SQL page-level corruption: page-level corruption occurs when the information or data stored in the header, body, or slot array of a database page is altered such that the user cannot access the page. Page-level corruption can happen due to reasons like hardware issues, disk/sub-system failure, malware, faulty updates and patches, etc.
• Boot page corruption: this is a more critical case of SQL database corruption as it concerns the boot page. There is only one boot page per SQL database, and it stores the metadata for the entire database. So, its corruption can affect the whole database file. Further, DBCC CHECKDB or page-level restore cannot fix the boot page corruption. This limitation is given the fact that the boot page stores Meta information like the current version, database ID, checkpoint LSN, etc.
• Non-clustered index corruption: this type of corruption is associated with SQL Server 2008 and later versions. It typically occurs when a SQL DBA attempts running a complex UPDATE statement with NOLOCK hint against a table. Non-clustered index corruption leads to incorrect reading of the SQL database query or multiple read operations on the same value.
• SQL database in suspect mode: SQL database suspect mode is a frequent issue DBAs face due to damage in the primary file group file, which stops the database recovery during SQL Server startup. A SQL Server may mark the database in SUSPECT mode after detecting a problem in the log file due to reasons like hardware malfunction, disk space issue, system crash, etc. A database in suspect mode fails to perform the read/write operations leading to downtime.

This Microsoft article provides more information on these errors and documentation of the DBCC CHECKDB function being used in the following SQL scripts.  Below is an excerpt from this Microsoft help article.  If you have a known good backup, you should first restore the database using that backup.

Microsoft always recommends a user restore from the last known good backup as the primary method to recover from errors reported by DBCC CHECKDB. The REPAIR_ALLOW_DATA_LOSS option isn't an alternative for restoring from a known good backup. It is an emergency last resort option recommended for use only if restoring from a backup isn't possible.

Step 1: Run the script below to see what table(s), view(s), and/or index(es) are impacted by the error.

USE Master
--Replace [DbNameHere] with the name of your database
GO

DBCC CHECKDB ([DbNameHere]) WITH ALL_ERRORMSGS, NO_INFOMSGS

This script will return a lot of information if there are errors in the database, but the most pertinent will be found near the end and it will give some indication as to what is corrupted. 

If there are errors with your database, you will see something similar to screenshot below.

Step 2: Execute the following script. 

This tells SQL Server to attempt to repair the issue(s) without allowing any data loss. 
Warning:

Although Microsoft claims this is a lossless procedure, any attempt to repair corrupt data inherently comes with some risk of data loss.

USE MASTER
--Replace [DbNameHere] with the name of your database
GO

ALTER DATABASE [DbNameHere] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE MASTER

GO
--Replace [DbNameHere] with the name of your database
DBCC CHECKDB (DbNameHere, REPAIR_REBUILD) WITH ALL_ERRORMSGS

Step 3: Repeat Step 1 to see if the issue was resolved by this script. 

If the issue was resolved the script should return something similar to the image below with no errors listed.

Step 4: Execute the following script

WARNING: This script WILL ALLOW DATA LOSS!  

USE MASTER
--Replace [DbNameHere]  with the name of your database
GO

ALTER DATABASE [DbNameHere] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE MASTER

GO

DBCC CHECKDB (DbNameHere, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS

Step 5: Repeat Step 1 to see if the issue was resolved and review your data to assess the level of data loss.

If this script does not resolve the issue, or the issue recurs soon after being corrected, then the source of the issue is likely drive failure. Assess the health of the drive(s) your database is stored on and make the appropriate adjustments.