Guide for Checking Corruption and Repairing Database in SQL Server

MS SQL database consists of a collection of tables that store data in an organized and systematic manner. Like other database files, MS SQL database files are also susceptible to corruption and consistency issues. In case of corruption in the database, you may face issues or random errors when trying to access the database file. Sometimes, you neither receive any error nor warning message. However, if you’re facing any issues while accessing the SQL database or the database goes into the suspect mode, then there are chances that the database is corrupted. So, you can check the database for corruption and other issues. In this guide, we will discuss the ways to check corruption in the SQL database. Also, we will mention some effective methods to repair the corrupt database.

Understanding the Causes of Corruption in SQL Server Databases

A database can get corrupted due to internal and external factors. Some common factors include:

• Improper termination of system
• Power outage while working on the database
• Hardware failure or software bugs
• Insufficient storage space on the hard disk
• Malware infection in the system hosting the database

Common Signs or Symptoms of Corruption in SQL Databases

Here are some signs and symptoms that indicate that your database is corrupted:

Random Errors when Opening/Accessing the Database

If your SQL database is corrupted, you may encounter error codes or messages while trying to open or access the database file. Some common error codes related to corruption in SQL database are: 823, 824, 829, 2508, 2511, 8993, and 7995.

Performance Issues with the Database

If your database is corrupted, it might run slower than usual or closes abruptly when you are working on it. You might also face unexpected delays or timeouts when opening the database file.

Database Moves to the Suspect Mode

In case of corruption or damage, sometimes, the database goes into the suspect mode. In this mode, the users are not able to access the database.

Ways for Checking Corruption in SQL Server Database

If you’re facing any issues with your SQL database, then you can immediately check it for corruption to avoid any further damage. Following are the ways you can use to check corruption in SQL Server database.

1. Run DBCC CHECKDB Command

MS SQL Server provides the DBCC CHECKDB command that helps you to check the overall integrity of the database, including corruption in tables, pages, and indexes. Here’s how to use the command to check corruption in SQL database:

sql        

DBCC CHECKDB ('YourDatabaseName');        

GO        

If there is corruption in the database, the command will return consistency errors and display detailed error messages. It will also recommend options to resolve the identified issues.

2. Check the Suspect Pages Table

SQL Server stores the details of corrupted pages in the msdb database in the suspect_page table. You can check the suspect_page table to identify the corrupted pages. For this, run the below command:

sql        

SELECT * FROM msdb..suspect_pages;        

GO        

How to Repair Corrupted SQL Server Database?

If you’re facing any errors related to corruption or the above ways show corruption in the database file, then follow the below methods to repair and recover the database.

1 - Restore the Database from Backup

If you have a healthy and updated backup of the corrupted database, you can easily restore it.? Here’s how to restore the database backup file using the Transact-SQL command:

RESTORE LOG <database_name> FROM <backup_device> WITH NORECOVERY;??        

RESTORE DATABASE <database_name> WITH RECOVERY;?        

GO        

Alternatively, you can also use the SQL Server Management Studio (SSMS) to restore the backup (BAK) file.

2 - Repair Corrupt Database with DBCC CHECKDB Command

In case the backup is obsolete or not available, then you can repair the corrupted database using the DBCC CHECKDB command. You can use different repair options with the command, depending on the severity of the corruption in the database. Here’s how:

REPAIR_REBUILD

You can use the REPAIR_BUILD option with the DBCC CHECKDB command to resolve minor issues in SQL database. This command will rebuild the corrupted indexes. Here’s how to run the command:

sql        

DBCC CHECKDB('YourDatabaseName', REPAIR_REBUILD);        

GO        

REPAIR_FAST

You can use the DBCC CHECKDB command with the REPAIR_FAST option for quickly repairing the database. This repair option maintains backup compatibility syntax and may not resolve complex corruption issues in SQL database. Here’s the command:

sql        

DBCC CHECKDB('YourDatabaseName', REPAIR_FAST);        

GO        

REPAIR_ALLOW_DATA_LOSS

If the above options fail to fix the issues with the database, then you can use the REPAIR_ALLOW_DATA_LOSS option with the DBCC CHECKDB command. Here’s is the command:

sql        

DBCC CHECKDB('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);        

GO        

The REPAIR_ALLOW_DATA_LOSS repair option deallocates the pages and rows in the table. It causes data loss and does not guarantee complete data recovery.

An Advanced Tool to Repair Corrupt SQL Database

If you want to repair corrupt SQL database file without any data loss, then use an advanced SQL repair tool, like Stellar Repair for MS SQL. It is a professional SQL repair tool designed to repair corrupt SQL database (both MDF and NDF) files easily. It can recover all the objects, including deleted records, from the corrupt SQL database, with complete integrity and precision. It even allows you to recover specific data from the database. It also provides an option to preview the data before saving. It can save all the data recovered from the database to a new SQL database file.

Highlighting Features of Stellar Repair for MS SQL

• Supports MS SQL version 2022, 2019, 2017, 2016, and earlier.
• Repairs corrupt MDF/NDF files with precision.
• Recovers all the objects from the database file, including tables, triggers, indexes and even deleted records.
• Allows saving the recovered data in a new database file or an existing database file (offline) and formats, like Excel, CSV, and HTML.
• Offers a Search feature to find and recover specific objects.
• Provides preview of the recovered data before saving.

?

Tips to Prevent Corruption in SQL Database

You can follow the given measures to prevent corruption in the MS SQL database:

• Make sure to regularly check the hard disk status on the system hosting the SQL Server.
• Always have a UPS (Uninterruptible Power Supply) to prevent power-cut problems.
• Make sure your operating system and SQL Server are updated with the latest updates and security patches.
• Install the best anti-virus/anti-malware software to prevent the system from virus or malware attacks.

Conclusion

In this article, we have discussed the ways to check corruption in SQL Server databases and the methods to repair and recover the corrupted databases. The native methods in SQL Server can help you repair the SQL database but they are time-consuming and involve the risk of data loss. As an alternative, you can use Stellar Repair for MS SQL. It can help you quickly repair the corrupt MS SQL database and restore all its objects with full integrity. You can download the demo version of the software to evaluate its functionality.