"Database integrity checking failed. Stopping backup operation." - Error message is displayed while running full data backup at POS

SYMPTOMS

The following error is shown during a full database backup in GINESYS POS - "Database integrity checking failed. Stopping backup operation."

CAUSE

This error occurs due to a database corruption almost in all cases due to a previous abnormal shutdown of the system. This article might help you recover from the error if the nature of the inconsistency is recoverable.

RESOLUTION

Phase 1: Preparing to start database corruption recovery

1. Verify that the error reported by the user is correct. Go to Application Control > Administration > Database and click on Take a FULL Backup Now and verify whether the issue reported actually exists and is same in the nature.
   
   
2. Verify if the error is corrected by rebuilding the indexes. Refer this article to understand how to rebuild the indexes from the software. Once the indexes are built, please verify if the error persists. Proceed to the next step if the error still persists.
   
   
3. Verify your internet connection and attempt to run a Data Sync if possible. This step, if succeeds will send all pending data to HO for safety.
   
   

4. Run the following script in SQL Server Management Studio on the POS database to check the object count in the database. This step is very important to detect whether any data loss is generated by attempting to repair the corruption.

   SELECT ta.name TableName,SUM(pa.rows) RowCnt
   FROM sys.tables ta 
   INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
   WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
   GROUP BY ta.name,ta.object_id
   ORDER BY ta.name

5. In the query output grid below, right click on the grid and click Select All to select all the output rows. Once all rows are selected, right click again on the grid and click Copy with Headers option.
   
   

6. Open an Excel file and paste all the selected output rows to Excel. Save the file to a suitable location.
   
   
7. Stop the GINESYS POS Data Sync service.
   
   
8. IMPORTANT: Stop the SQL Server Engine service and take a backup of the relevant data files (MDF and LDF) into another location. GINESYS will not take any responsibility of data recovery if MDF and LDF data file backups are not kept in separate location. Restart the service once the data file backup is done. 

Phase 2: Database recovery

1. Ask all users to logout from the system
   
   
2. Open SQL Server Management Studio and login with 'sa' user.
   
   
3. Take the database into single user mode. If the following statement does not succeed then do not proceed to next step without resolving this step. 
   
   ALTER DATABASE <databasename> SET SINGLE_USER
   
   NOTE: If the above statement gets hanged then click the 'Stop' icon at the top of the SQL Query window and run sp_who2 to identify which users/terminals are connected to the system
   
   
4. Run the following script to repair the database
   
   DBCC CHECKDB ('<databasename>', REPAIR)
   
   
5. Run step 4,5,6 of the Phase 1 preparation step to get the record counts copied into the same Excel file but in different columns. 
   
   
6. Create another formula column like = (NewColumn - OldColumn) to detect whether any data loss has occurred. If you see that any record is removed in any table, do not proceed further and get in touch with GINESYS Technical Support immediately. 
   
   
7. If the comparison results are OK then put the database back into multi user mode by running the following command.
   
   ALTER DATABASE <databasename> SET MULTI_USER
   
   
8. Run the following command to verify that database consistency checks now pass without reporting any errors.
   
   DBCC CHECKDB ('<databasename>')
   
   
9. If no error is reported, run software and try taking a backup and verify if the error still persists.
   
   

This should resolve your issue, in case you face it again please contact our Ginesys Care portal - https://care.ginesys.in/