A table in the SQL database can get corrupt due to storage issues, like insufficient space on the drive, bad sectors on the drive, etc. Besides this, a malware attack, sudden system shutdown, and accidental data deletion are other factors that may cause table corruption. Running DBCC CHECKDB can help you detect corruption in the table. For instance, a table corruption error looks something like this:
Methods to Get Data from Corrupt SQL Table
Use the below-given methods in the same sequence to extract as much data as you can from the corrupted table. However, to avoid losing any data, consider using a SQL recovery tool.
Method 1 – Run DBCC CHECKDB Command with Repair Option
As you can see in the error message above, repair_allow_data_loss is suggested as the minimum level of repair to fix the error. Run DBCC CHECKDB with the repair option for the corrupt table (as given below).
| dbcc checkdb (table_name, repair_allow_data_loss) |
Replace ‘table_name’ with the name of the problematic SQL table. If this doesn’t work, try the next method.
Method 2 – Move Data from Corrupt Table to New Table
Note: This method will only work if the database is accessible. If the database is also affected, use method 3.
Script out all objects in the current database and create a new database with a new table. Move all the data except for the corrupt table to the new database. Once you are done, try moving the data out of the table into the new one. The detailed steps are as follows:
- Right-click the database containing the corrupt table and expand Tables.
- Right-click on the table, click Script Table as > DROP And CREATE To > New Query Editor Window.
- Click Execute to run the query. Doing so will drop the selected table and re-create it.
If the problem persists, skip to the next method.
Method 3 – Use Bulk Copy Program (BCP) to Export the Table Data
Bulk Copy Program (BCP) is a command-line program that helps extract data from a SQL database table and export it to a user-specified file format. Essentially, you can use the BCP utility to export all the good data out of the corrupt table into a data file. You can then import the data back into a new table. Refer to this link to find out how to use the utility.
While this method might work, copying the data of a large table and importing it into a new table can take long hours or days to complete.
Better Alternative to Get Data from a Corrupt SQL Table
A better alternative to recover data from a corrupt SQL table is to use a SQL recovery tool, like Stellar Repair for MS SQL. The software can help perform faster recovery of SQL database tables. Essentially, the software repairs the database files (MDF and NDF) and recovers all the objects, including tables in a few clicks. It even allows recovery of deleted records. Here’s how the software works:
- Launch the software after downloading and installing it.
- Browse the database file containing the corrupt table. If you cannot find the file, use the Find option to search the file.
- Once the database file is uploaded, click Repair. In the ‘Select Scan Mode’ dialog box that pops up, choose the default standard scan option for faster scanning of the database tables. If the standard scan doesn’t work, use the advanced scan option.
- Clicking OK starts the repair process and displays a preview of all the recoverable tables and other objects.
- Select all or specific tables you want to restore and click the Save icon on the File menu.
- In the ‘Save Database’ dialog box, perform these steps:
- Save the recovered data in MDF, HTML, CSV, or XLS file format. If you’ve selected MDF file format, choose to save the data in a new or a live database. Next, enter the server details and location for saving the data.
- Click Save.
- A ‘Save Complete’ message box appears on completion of the repair procedure.
You can access the table data from the repaired file stored in the selected location.
Conclusion
If your SQL database table has turned corrupt, you can first check the backup to get the table data. In case the backup is not available or not working, follow the methods discussed in this post to get data from the corrupt table. However, the best and easiest way is to use a SQL recovery tool to retrieve all the table data.
Was this article helpful?
