Corruption in SQL database tables can occur at any time. However, it is a worrying situation for the database administrators (DBAs) as this puts the critical data stored in the tables at risk. Thereby, it is important to fix the corruption immediately. In this post, we will be discussing different methods to fix table corruption in SQL Server database. But before that, let’s understand the reasons behind table corruption.
Reasons for Table Corruption in SQL Server
Table corruption in SQL Server can occur due to the following reasons:
- Corruption in the storage media
- Malware/virus infection
- File header corruption
- Power interruption
- Sudden system shutdown
- Disk failure
- Bugs in the disk driver
- Accidental data deletion
- Storing SQL database in a compressed folder
Methods to Fix Table Corruption in SQL Server
Here are some effective ways to fix the corrupt tables in SQL Server and recover the data.
Method 1: Recover Table Data from Database Backup
If the tables get corrupted, you can recover the tables’ data from the available healthy database backup. Follow the below steps to restore SQL Server backup:
- Open SQL Server Management Studio (SSMS).
- Click on the Databases folder and then click the Restore Database option.
- Click on the ‘From device’ option, select File, andclick Add.
- In the ‘Locate the Backup File’ wizard, select the SQL backup (BAK) file.
- In the Restore Database window, select the name of the database or type the database name.
- When the SQL database is restored successfully, a confirmation message will be displayed. Click OK.
This method works if you’ve a recent healthy backup. If you don’t have a backup, then follow the next methods.
Method 2: Use the DBCC CHECKDB Command
You can use the DBCC CHECKDB command to repair the corrupt table. Follow the steps mentioned below:
- Step 1: Set Database to Single User
Note: Ensure that the AUTO_UPDATE_STATISTICS_ASYNC setting is turned off at the time of setting up the database to single user mode. If the Auto-Update option is enabled, then the thread that functions to update the statistics can create problems.
- Step 1: Open Microsoft SQL Server Management Studio (SSMS).
- Step 2: Now, connect the database via Windows Authentication.
- Step 3: Go to the Object Explorer and connect with an instance of the SQL Server database engine.
- Step 4: Choose the SQL Server database under which the table needs to be repaired and right-click on it.
- Step 5: Select the Properties option.
- Step 6: In the Database Properties dialog box, click on Options.
- Step 7: Choose the Single option under the Restrict Access section.
Note: If the users are connected to the selected database, an Open Connections message will be displayed on the screen. Click Yes to disconnect all connections.
- Step 2: Repair Damaged SQL Table
After setting the database to Single User mode, run the DBCC CHECKDB command with the Repair option (see the below example).
- dbcc checkdb(‘Name_of_Database’, REPAIR_REBUILD)
If the above query cannot repair the database, run the following query.
- dbcc checkdb(‘Name_of_Database’, REPAIR_ALLOW_DATA_LOSS
Once the SQL Server database is repaired, set the database to multi-user mode by running the below command:
- ALTER DATABASE database_name SET MULTI_USER
Method 3: Use a Professional SQL Database Repair Tool
If the above methods did not work for you, you can use a professional SQL database repair tool, like Stellar Repair for MS SQL. It is an advanced tool that can repair corrupted SQL database (MDF) files and recover all the objects, including tables, rules, triggers, functions, and stored procedures. The software is capable of recovering even the deleted records from the SQL Server database. It can save the recovered data to a new SQL Server database, CSV format, and SQL compatible scripts. Using this tool, you can easily fix table corruption and recover the data from it. The main features of this tool include:
- Allows to preview the repaired file before saving
- Recovers the data with 100% integrity
- Helps save data at a user-defined location
- Compatible with SQL Server 2022, 2019, 2016, 2014, and lower versions.
Conclusion
Corruption in SQL Server database tables can occur due to reasons, like subsystem failure, SQL Server crash, virus or malware infection, bugs in SQL Server, etc. The corruption leads to data inaccessibility and therefore, it is important to fix the table corruption. You can restore the database from an updated backup or use the DBCC CHECKDB command to repair the database.
If the backup is not available and the repair command does not work, the most reliable solution to fix the SQL Server database corruption is by using a third-party SQL database repair software, like Stellar Repair for MS SQL. This tool can help you repair the corrupt database and restore all the data, including tables, stored procedures, triggers, and functions with complete precision and integrity.