How To Repair a Corrupted SQL Database
Your SQL database can get corrupted for many reasons, including power cuts, hardware crashes, or buggy code. Regardless, you want to restore the database with minimal possible downtime. We’ve outlined two main ways to fix a corrupted SQL database. We’ve also highlighted a few common challenges you might run into during the recovery process.
Step 1 – Attempt Repair with SQL Server Management Studio (Optional)
In an ideal world, you’d be able to fix a corrupted database locally, quickly, and for free. The SQL Server Management Studio (SSMS) offers this possibility. However, it only works for minor corruption issues. There’s also the possibility of data loss with some commands. So you’ll most probably need a specialized recovery tool in most cases.
For this reason, we’ll only go through the basic steps required to attempt to repair/troubleshoot a corrupted database using this method.
You’ll first need to put your database in EMERGENCY mode. This mode gives the administrator read-only access to the database. To do this, open the SQL Server Management Studio (SSMS) by going to Start > All Programs > Microsoft SQL Server > SQL Server Management Studio.
Then, run the query ALTER DATABASE [data_base_name] SET EMERGENCY.
You can now analyze the database for errors by executing the DBCC CHECKDB (N database_name) WITH ALL_ERRORMSGS, NO_INFOMSGS command. You’ll get back a results report containing the found errors and the recommended minimum level of repair. You’ll find this information at the bottom of the message summary.
There are a few Transact-SQL repair command options at your disposal at this point. The safest option is the REPAIR_REBUILD command. The other option is the REPAIR_ALLOW_DATA_LOSS command. As you might have already guessed, the latter option may delete some database pages. Therefore, Microsoft doesn’t recommend the latter option unless it’s mandatory.
Next, you’ll need to put the database in SINGLE_USER mode before attempting repairs. This way, other users cannot modify the data while the restoration is ongoing. Again, you can use a Transact-SQL command ALTER DATABASE database_name SET SINGLE_USER to do this.
You can now run the DBCC CHECKDB (N ‘database_name , REPAIR_REBUILD) command. Finally, go back to MULTI_USER mode if the repair was successful. You can do this with the ALTER DATABASE database_name SET MULTI_USER command.
However, there’s a good chance that this method won’t work for most severely corrupted databases. So if you know your database is seriously corrupted, it might be worth skipping this step altogether and going right to step 2.
Step 2 – Choose a Good Database Repair Tool (Recommended)
The safest bet for repairing a corrupted database is to use a specialized database repair tool. This method is quick, effective, and almost always foolproof. Some of the considerations to keep in mind when choosing your SQL repair tool include:
- The tool should be compatible with your SQL version
- It should easily connect with your servers and applications
- The tool should support your operating system, especially if you’re using a legacy version
- Should be proven to repair common SQL database errors
Some of the SQL database repair tools that meet these criteria include SysTools SQL Recovery Manager, Kernel for SQL Database Recovery, and Stellar SQL Database Repair.
Step 3 – Download Your SQL Repair Tool
The tool you choose for the following steps is entirely up to you. I’m going with SySTools SQL Recovery Manager. This tool is affordable compared to the other choices and has a proven track record of repairing most minor and major SQL server errors. It is also effective for recovering advanced data types and is easy and intuitive to use.
Aside from fixing SQL database corruption issues, this tool also lets you:
- Decrypt encrypted SQL Scripts
- Generate an activity log report
- Open and preview MDF elements
- Reset lost SQL SA password
- Analyze the complete log database
- Restore single or multiple back-ups
Whichever tool you choose, simply go to the provider’s main website to purchase or download the software. SySTools SQL Recovery Manager costs $1499 for a site license. You’ll also be able to run the software on multiple computers regardless of their physical location.
Finally, SySTools offers a 30-day money-back guarantee just in case you’re concerned about paying upfront before you see results.
You’ll receive an email with the software download link and activation key as soon as you make your payment.
Step 4 – Run Your SQL Database Repair Tool
Most repair tools will ask you to choose the software destination location during installation. Otherwise, the default location is C:\Program Files\SQLTool. Go to the appropriate location and open the repair tool to get started.
Step 5 – Scan the Corrupted SQL Database
You’ll need to add the corrupt database to the recovery software and scan it for issues. With SySTools, simply click Open in the toolbar, select your Main Database File (MDF) from the dropdown menu, and click Open to add the file.
Next, choose your scan option. SySTools offers a quick scan and an advanced scan. The quick scan is faster and works for minor corruption. You can use the advanced scan if you suspect severely corrupted files.
You’ll also need to choose the appropriate MDF file version, such as Microsoft SQL Server 2005 or SQL Server 2008 format. Fortunately, the software can detect this automatically if you’re unsure of the file version.
Click OK to load the file and click Close when the scan is completed successfully. Additionally, the software gives you the option of saving the scanned MDF file in STR format.
You’ll need to repeat the process for the secondary database (NDF) files. Simply click the NDF Options tab to add the files. You can let SySTools automatically detect the secondary database if you are unsure. Alternatively, you can click the Add Files or Add Folder to add the files manually, either one by one or multiple files simultaneously.
Click OK after adding your files and then Close when the scan is completed. Again, you have the option of saving the scanned files in STR format if you wish.
SySTools lets you preview your database items if you’d like. The component previews include database tables, stored procedures, rules, triggers, and functions.
Step 6 – Transfer Your Recovered Files to Your Database
It’s time to export your recovered files back to your SQL database. SySTools lets you do this in a few clicks.
First, head over to the toolbar and click Export.
Now click the SQL Server Database option.
You’ll be requested to fill in your credentials, such as user name, server name, database name, and password.
Now choose the database components you’d like to export. You can also export database files with schema and data or only with schema.
Click the Export/Save button to export your recovered database. You’ll also need to choose the folder location to save the .sql file. Finally, click OK to initiate the export process.
You can now find your fixed database in the saved location. You’ll also see a ReadMe.txt with details of the scripts you need to run to create the recovered database.
Follow the three simple steps, and you are ready to access the fixed database in the saved location.
Common Problems When Repairing a Corrupted SQL Database
You may encounter a few common challenges while repairing a corrupted SQL database. Learning about these challenges beforehand ensures that you are prepared for any eventuality.
Improper Repairs That Worsen the Problem
Knowing what not to do with a corrupted SQL database is just as important as knowing what to do. Some actions may seem sensible but actually, make the situation worse. Some of the things that you shouldn’t do with a corrupted database include:
- Moving the corrupted SQL server to a new server – This process is tedious and still won’t get you the desired results. The corrupted server won’t attach to the primary or the second server.
- Rebooting the SQL server – Unfortunately, turning the server off and on again isn’t a plausible solution. The database will only go through crash recovery with no fixes. You’ll only be delaying the inevitable.
- Clearing the procedure cache – This action will only make the problem worse.
- Attaching/Detaching SQL Database – This purported repair is another myth that will compound your problems. Attaching and detaching your SQL database can cause irreparable damage and worsen corruption.
You’re likely to hear many more myths about how you can fix a corrupted database. It is best to stick with a known repair and recovery tool that can guarantee results.
Frequent Database Corruption
You may find that your database crashes more frequently than you’d like. This situation is likely to recur until you resolve the root cause of the corruption in the first place. Some of the common reasons why databases crash includes:
- Upgrading from an older SQL version to a newer one
- Hardware failure
- File header corruption
- Malware attacks
- Sudden system shutdown
- Changes in the SQL account
However, it is often difficult to pinpoint the cause of the corruption without conducting an analysis. You can start by reviewing the Windows Event Log, SQL Server Error Log, or SAP System Log. This is where all SQL Server error logs are recorded. You’ll get an error code like 823 or 605 to help you identify the nature of the error and the probable cause.
Alternatively, you can use the DBCC CHECKDB execution state to find out the details of the corruption. This option will also offer recommended fixes. In addition, you may be able to fix non-critical errors such as corrupted index pages using the SQL Server Management Studio.
Some corruption, such as data and metadata corruption, cannot be fixed from within the database using this method. Instead, you’ll need to restore the database from the last known clean backup or use a specialty database recovery tool like SYSTools.
It’s good to find out the root cause before proceeding with the repair. For instance, you may find that the database crashed because of bad memory. Proceeding with the repair before replacing all memory may cause new corruption.
Missing or Non-Existent Database Backup
One of the most straightforward fixes for a corrupted SQL database is restoring it from a backup (.bak file). However, the backup may not be available for several reasons. Recovering a database from a backup may also not be possible if the backup is also corrupted.
SQL database recovery tools provide a workaround for this problem. For example, SySTools SQL Recovery software lets you recover your database, even without a backup. You’ll also be able to fix the corruption and restore your database quickly.
Alternatively, you could try to restore your database using the SQL Server Management Studio (SSMS) method we outlined in Step 1. But this method is complicated. There’s also the chance you could delete crucial files permanently if you use the REPAIR_ALLOW_DATA_LOSS command. Lastly, there’s no guarantee that you’ll recover your corrupted database using this method.
Be sure to take frequent backups during the repair if you choose the manual method. Then, you’ll be able to preserve some of the working databases if something goes wrong. It’s also a good idea to document all the steps you take during the repair.
Now is also an excellent time to develop a backup strategy. This way, you can quickly restore your database in case of a hardware failure in the future. However, remember that outdated databases aren’t deleted every time you take a full backup. So ensure that you delete the old databases regularly to conserve disk space and avoid confusion during future recovery.
It’s worth having at least two copies of your database backup. Ideally, these backups should be in two mediums. For example, you can save one backup in a USB drive and another in a secure cloud backup solution. It is also good to keep one copy offsite.
Incompatible SQL Server Versions
You may get an error when restoring a corrupted database using a backup. For example, if your backup was created using a newer SQL Server version than the original database, this might happen. This situation may also occur if you select an incorrect server version when using an SQL database restoration tool like SySTools Tools.
The quickest solution is to use software that automatically detects the server version. SySTools offers this capability. It is worth noting that your database version is independent of your SQL Server version. For example, upgrading to a newer SQL Server version doesn’t mean that the database inside the new server version is equally updated.
Safeguarding the integrity of your SQL database is paramount, given the factors that can lead to corruption, from power interruptions to code issues. When faced with a corrupted SQL database, the priority is swift restoration with minimal downtime. For a complete guide to SQL Server Disaster Recovery, visit here.
