Simple Steps to Check the Integrity of SQL Server Database

👤 Andrew Jackson | Published: May 8th, 2023 | SQL Server 2016 | 5 Minutes Reading

There are often errors and bugs encountered in MS SQL Server 2000, 2005, 2008, 2008 R2, 2012, 2014 and 2016. The first step to recovering corrupt database files is to run an integrity check. These are a set of commands that perform checks on the MDF and LDF files. It returns a list of errors and provides a proper course of recovery. This post describes all the ways on how to check the consistency of database. It explains all the steps necessary in repairing databases based on their priority level. According to which, measures can be undertaken to resolve the issue and restore database files and corresponding data.

Reasons to Check Integrity of SQL Server Database

Following are some of the problems that may lead an administrator to check integrity of SQL Server database and consistency:

One of the servers has issues or bugs in an environment with multiple SQL servers.
Potential inconsistencies between the database and server primary and log files.
Run DBCC CheckDB on database to check all logical and physical integrity of all objects in the specific database.
Remove any data corruption causing cause all sorts of issues within the database.
Frequently failed SQL statements, incorrect results or SQL instance not working.

Disadvantages of Running DBCC CheckDB Command

There are instances where the integrity check on the complete database may prove disadvantageous. Here is why:

Time Consuming: It takes hours to completely check each and every logic of MS SQL Server.

Data Integrity Not Ensured: Running DBCC CheckDB Command may change some important data that may cause more problems than usual.

Not a Well-Defined Way: It is not recommended to run a check a complete database without proper plan or backups.

Difficult to Identify Errors: The DBCC CheckDB command returns a list of hundreds of errors. It is difficult to pinpoint the exact error location and fix it.

SQL Server Database Consistency Check

Instead of applying a fix to the entire database, the SQL Server recovery can be done by breaking down databases based on priority. Following are the different priority level and ways to recover data.

Solution#1 High Priority Databases

These are databases that hold important company data which can incur MS SQL Server consistency issues. Any corruption in this database can create heavy revenue losses and millions in damages. This environment cannot have a single point of failure or it may lead to lawsuits and legal actions.

Here are the major steps in order to check SQL Server database consistency in high priority databases:

Use an Active-Active environment for database management.
Then, taken down one server for maintenance and keep other running,
Run integrity check on a frequent basis, almost daily.
Before building large environment by self, consider all pros and cons.
Take careful considerations and precautions while building a new model.
Consult support team on every level and choose a route accordingly.

Solution#2 Medium Priority Databases

These contain information like monthly payment information. Some data loss is fairly acceptable in such a case. Customers can be reimbursed based on the technical glitch on the company’s part. The cost inured is within acceptable parameters to the company and no class action will be taken. So, follow the steps below on how to check the integrity of SQL Server with medium priority databases:

Run an integrity check before taking and backups
Keep the databases in full recovery mode.
Take frequent backups of complete database and transaction logs.
Set up Availability groups that are AlwaysOn.
Now, check database integrity using DBCC CheckDB.
Restore from the most recent backup log in case of command failure.
Check the error log and drop the corresponding non-clustered indexes and recreate them.
If the error is in a complete table, drop the table and recreate it from a backup.

Solution#3 Low Priority Databases

In these cases, data loss is completely acceptable. These are usually marketing or employee records that are stored on paper or in some digital manner. Information can be easily reloaded in case of data loss after running DBCC CheckDB command. Here are the ways to dealing with this situation:

Apply Repair_Allow_Data_Loss as a course of action suggested by the integrity check.
Restore database from an old backup.
Also, confirm with the customer and notify them about the information update.
Keep multiple copies of backups in different locations.
Create a regular restore schedule for your backups.

Solution#4 Use an Expert Solution

There is a more direct approach to recover database after DBCC consistency check. It is simple yet effective third-party tool that makes the entire task more professional and easier. It is the SQL Repair that can repair and recover all SQL Server database without data loss. It is one of the best tools in the market to scan and recover multiple MDF and LDF files in one go.

Conclusion

The manual solutions provide an easy fix to all database issues. It is highly recommended that all organizations, big or small take regular backups and run DBCC check. This post helps a user understand how to check the integrity of SQL Server Database without any data loss. A completely effective, expert solution is also described at the end to make the task more direct and simpler.