St. Robert, MO, USA Police Department Repairs Large MS SQL Database by using Stellar Toolkit for MS SQL

Oct 07 , 2022
Print
Download

St. Robert Police Department is the law enforcement authority in St. Robert- Missouri, which is responsible for improving the quality of life by providing a safe and secure environment in the city.

St. Robert Police Department had been using a sophisticated SQL Server database to serve various needs such as providing police performance data to Home office, record keeping, reports booking, accessing reports, and data lifecycle management.

Approx. 30 people in the police department had been using this SQL database, with major dependencies on the system to perform their duties on a daily basis. Of late, some users had reported that they were unable to fetch any information using web-based interface of the SQL Server reporting system; the problem had surfaced without any prior symptoms or precedence.

The system administrator diagnosed database corruption as the cause of this problem; initial investigation revealed that the web app was unable to connect with the backend database, which was most likely due to corruption. Apparently, the database had turned into Suspect Mode due a sudden power outage while the data center backup system also malfunctioned.

Root Cause Analysis

The administrator checked the SQL error log on suspect_pages to find that transactional log file of the database had turned corrupt which is a common issue associated with erratic power supply. Error code 823 that indicates a suspect page was listed down in the event_type for multiple pages; a significant number of pages in the database had been marked suspected as being bad.

article

Recovery Attempts

The system administrator sought to resolve this issue at multiple levels starting with recovery from backup, considering it's the least risky in terms of potential data loss.He was relying on SnapManager® - NetApp's integrated data management system for SQL - for backup; however, it turned out that the tool wasn't configured on the server in the first place, which came as a rude surprise.

Next, he attempted recovery from a 'QA' instance of the database by using T-SQL. Successful recovery with this method is possible only if the version and build for both production and test databases match, which unfortunately wasn't the case here. So, database recovery again failed due to a mismatch in the server versions.

As last resort, the administrator decided to use Emergency state for dealing with the database in Suspect Mode. Emergency state would allow direct access to the Suspect database and its configuration settings. It would also allow exporting the database and running DBCC CHECKDB commands to resolve corruption.

He used DBCC CHECKDB Command with REPAIR_ALLOW_DATA_LOSS option to fix the database which was already put in Single user mode as a prerequisite. Running this command was expected to rebuild the corrupt transaction log file and reinstate the database to ‘Online’ state.

However, after rebuilding the transaction log file and switching over the database to multi-user mode, several anomalies were found in data relations when checked with DBCC CHECKCONSTRAINTS. There was also a loss of transactional consistency in the target database. Due to the large size of corrupted database (~200GB), DBCC CHECKDB couldn’t effectively repair the database and now there was no more solution left to address this problem. St. Robert Police Department had been struggling with major downtime for days now with potential loss of database storing several gigabytes of crucial information.

Business Need

Following were the key business needs:

Repair corrupt MS SQL database and restore it to original ‘clean’ state without any loss in database integrity; DBCC CHECKDB had already affected integrity of the database.
Reinstate database access by the earliest, with minimal manual efforts and time; the client had already lost several hundred person hours in resolving the issue.

Solution

Now evaluating 3rd party database repair software or utilities, the system administrator decided to try Stellar Toolkit for MS SQL. This software toolkit specializes in MS SQL database repair and recovery from severely corrupt SQL Server and backup (if available).

The administrator downloaded a free trial of the software to ascertain its effectiveness. The trial software parsed the database and reconstructed all its elements which were confirmed having rebuilt into their original state via the Preview feature.

Now having ascertained that the database was totally restored like new, the administrator contacted Stellar’s customer support team to understand the next steps.

He purchased the license key and then activated the software to save the restored database on the local disk. The entire SQL database repair and recovery process was completed in a few hours, with nominal human intervention. The police department was able to use the database system as earlier in a matter of 24 hours.

Key Benefits

This was a great win for the client as the entire 200 GB corrupt database had been restored to its original state in the least possible time, without wasting efforts or losing database integrity.

Also, having procured the Lifetime license of the software ensured that the client organization was now 100% secured from such potential MS SQL database corruption situations in future.