How to Recover Data from a Corrupted SQL Database?

Are you facing problems when opening/attaching SQL database after sudden power failure? It usually occurs due to corruption or inconsistencies issues in the database. In this article, we will discuss the methods to recover data from a corrupt SQL database. 

MDF and NDF are the common file formats used by SQL Server to store the objects. These database files are prone to corruption or damage. They may get corrupted due to hardware or software failure, large size, sudden termination of system or application, virus/malware infection, software bugs, and various other reasons. When these files get corrupted, you may face various errors and issues like:

• Fail to execute query related to corrupt SQL database
• The database can become inaccessible 
• Errors like “Could not open new database ‘Your_DatabaseName’. CREATE Database is aborted”, Msg 5105, Level 16, State 2, Line 1, inconsistency errors like 823, 825,824, etc.

To recover data from corrupt SQL database, start with restoring your backup (.bak) file. If the backup is not available, then use DBCC CHECKDB. This command can help you repair minor issues, but it may not guarantee complete database recovery, especially if pages or rows are severely corrupted. When MDF/NDF files are highly corrupted, you can use a professional SQL repair tool to recover data and restore database quickly and accurately. 

Effective Ways to Recover Data from Corrupted SQL Database

To repair and recover data from damaged, corrupted, or inconsistent SQL database, follow the methods mentioned below.

1 – Restore Backup File 

If you have created the backup (.bak) file, then you can restore the SQL database whenever it is required. Restoring backup is a reliable option in MS SQL when the database goes into suspect mode or becomes unavailable. It helps you bring your database to a normal state while preserving complete consistency.

Before Restoring BAK file in SQL Server, first make sure:

• You have all permissions on the backup file.
• Your BAK file is free of corruption and is complete. 
• Backup file is compatible with the version of SQL Server on which you’re trying to restore. 

To restore the .bak file using Windows PowerShell, run the following command:

Restore-SqlDatabase -ServerInstance [server-instance] -Database [database-name] -BackupFile [backup-file] -ReplaceDatabase

Alternatively, you can use the SSMS to restore SQL database from backup. 

Both these backup restore methods support restoring the backup of all types, like: 

• Full database backup
• Transaction log backup

Things to consider while restoring SQL database backup file:

• There should not be an active connection or active windows running on the database that need to be restored.
• You can’t restore an encrypted database without an asymmetric key or a certificate.
• You need an active transaction log file if the database uses the FULL or BULK-LOGGED recovery model.
• The backup file should be readable.

2 – Use DBCC CHECKDB Command 

If your backup file is not available, is corrupted, or not complete, then you can use the DBCC CHECKDB command with repair option, like REPAIR_ALLOW_DATA_LOSS. It is the most preferred method when it comes to the task of repairing the SQL database. It uses the log-based recovery techniques to help you recover the database. You need to change the state of the database to emergency mode before using it. This helps you access the database in read-only mode. Also, set the database to single-user mode. 

Here is the command to run DBCC CHECKDB:

DBCC CHECKDB (N ’admin5465’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; 

GO

To know the steps in detail to use DBCC CHECKDB utility to repair the SQL database.

Once you’ve repaired the database, you can again run the DBCC CHECKDB command to check its integrity.

Things to consider while running the DBCC CHECKDB command:

• Since DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS uses the transaction log file, corruption of that file can lead to database recovery failure.
• It can’t guarantee complete recovery of the database.
• It deallocates the pages or rows in the database, which can cause data loss.
• The success of this command varies with the level of corruption in the SQL database.
• Sometimes, it may take some time to execute or stop responding.

3 – Use Professional MS SQL Database Repair Tool

Using a professional MS SQL repair tool to repair the corrupt SQL database is one of the effective methods. Many SQL repair tools are available in the market. Select the most reliable one, like Stellar Repair for MS SQL. It guarantees integrity of the SQL database files, including MDF and NDF, while repairing. The tool allows you to safely recover all the data, including deleted records, from the corrupt SQL database. 

Benefits of using Stellar Repair for MS SQL:

• It has a user-friendly interface that allows you to repair SQL database quickly and efficiently.
• It allows you to preview the repaired database before saving. 
• It supports object-specific recovery. 
• It preserves collations while repairing SQL database file. 
• It supports recovery of complex user-defined functions and stored procedures with absolute precision.
• It provides flexibility to save the repaired SQL database file in multiple formats.
• It can repair and recover highly corrupted MDF/NDF files. 

Conclusion

Experiencing database attaching errors or issues when running DBCC CHECKDB are common signs of database corruption. Above, we have discussed some effective ways to recover data from corrupted SQL database. Minor corruption in database can be easily fixed using native SQL repair tools. To handle situations where complex corruption-based errors occur in the database, you can opt for professional SQL repair tools, like Stellar Repair for MS SQL. This tool can quickly repair damaged or corrupted SQL databases and recover all their objects, like tables, triggers, keys, and stored procedures, with complete integrity.