How to Repair a SQL Database in 2020

Dealing with database corruptions is an inevitability in the career of an MS SQL DBA. While there are plenty of ways to approach corruptions, including attempts at manual fixes and running away to Belize, there’s another alternative: third-party recovery software.

The right software, in fact, can both spare you time and frustration, and make you look pretty darn good to your corporate overlords or panicking clients. One of the best SQL database recovery programs we’ve vetted is a tool produced by IT software developer Stellar Data Recovery.

We’re not really all that surprised at how easy it is to use the software. That’s really been the trend with every Stellar product we’ve played with since evaluating the company’s flagship product, Stellar Data Recovery, which currently ranks first in our best data recovery software rankings.

Coming up, we’ll talk database repair in general, features you’ll get with Stellar SQL Database Recovery and run down the steps involved to repair .mdf and .ndf files. We’ll also take a quick look at the costs so you can figure out if Stellar’s product will fit into your IT budget.

Who Needs SQL Database Repair Software?

Most likely, if you’ve landed on this article, you know exactly why you need SQL database repair software: it’s designed to repair corrupt database files, both primary (.mdf) and secondary (.ndf). Such software is sought out by MS SQL admins or similar IT professionals either looking to recover from a disaster or planning to be prepared for one.

There is a range of issues that could lead to database problems, such as a corrupted or missing transaction log file, failing hardware, a virus attack, improper shutdown of the server and, naturally, user error. In such cases, you have to perform a recovery before you can access your MS SQL database or perform transactions.

If you have the expertise and the nerve, you can attempt a manual repair, using intact log files (.ldf) to do so. Log files, however, won’t always get you out of hot water, especially since those files can also be corrupted. You can also use commands like DBCC CHECKDB and DBCC DBREPAIR from the database console to fix minor issues.

For more dire circumstances, or for those not confident in their technical knowledge or in search of an easier path to recovery, a third-party recovery tool is the best solution.

While there are several tools out there for repairing .mdf and .ndf files, we chose to illustrate how to navigate these troubled waters using Stellar SQL Database Repair because we think it offers the best combination of features and usability.

We’ll get to the user experience in a bit but first, let’s talk general features to make sure this is the tool you’re looking for. First off, the software supports most versions of MS SQL Server, from 2016 and back.

The software can recover both .mdf and .ndf files and all objects contained within them, including tables, triggers, indexes, keys, rules and stored procedures. Tables with PAGE and ROW compression can be corrected by the Stellar tool as well.

Once the repair job is done, which works by scanning the database files for mangled areas and fixing the bit pattern, you can save the corrected files in MS SQL, .html, .xls and .csv formats. If you don’t require the entire file or need to get at a specific object quickly, Stellar SQL Database Repair also supports selective recovery of objects.

Prior to recovery, you can preview objects to make sure it’s what you’re looking for. You can also save a copy of the scan as a .dat to work with later.

We tested out all of the features to make sure they worked like Stellar says they do. We’ll walk you through some of the basic processes in a moment. First, let’s take a quick look at how much it’ll set you back.

Stellar SQL Database Recovery Licensing Options

Stellar currently sells two editions of its SQL Database Recovery software: Technician and Platinum. There’s also a SQL Database Toolkit that comes packaged with some other software that will appeal to MS SQL admins in need of building out their software library.

The costs might seem high at first but that’s because they’re good for life, unlimited databases and for use on multiple systems. You also get lifetime support. Additionally, the price is in line with similar — and, in our experience, inferior — products like Kernel SQL Recovery.

Stellar SQL Database Recovery Platinum adds SQL backup repair to fix corrupt .bak files, while the Toolkit plan includes both that and SQL password recovery.

You can try the software with a free trial, which will let you perform the scan and preview objects but not perform the actual recovery to create clean database files. Stellar also offers a 30-day money-back guarantee if the product doesn’t meet your needs.

Repairing an SQL Database

You can download the trial version of Stellar by clicking the green “free download” button rather than the “buy now” button on the product landing page. If you purchase a license, Stellar will email you a registration code that you’ll need to input prior to performing a recovery.

Once you’ve installed the software and boot it up, Stellar will remind you to stop your database server and copy the corrupt file to a different location. Click “ok” and you’ll next be asked to indicate the location of that file.

If you don’t know where your corrupt file has been stored, click “find” and the software will look for it for you, saving you precious time to do more important things, like research better enterprise sync and share options for your business. Read fast, though, since the scan will only take a minute.

In the list of returned files, highlight the .mdf or .ndf file you want to fix, then click “repair.” There’s a toggle to “include deleted records,” too, if you want to include those in your results.

The repair will kick off, with a progress bar and log showing you what’s going on near the bottom of the client. For us, the process finished in about ten seconds. Obviously, though, the length of the repair will depend on the size of your database. We used a small 10KB .mdf file to try the software out with.

Once the scan and repair is finished, click “ok” on the dialog box that pops up. You’ll need to register your copy to complete the repair if you haven’t already. A copy of the scan results will be automatically saved if you want to work with it later.

The repaired file will be loaded on the client console once the scan completes You can navigate objects in the repaired file using the tree menu on the left side.

You’ll find branches for tables, views and synonyms. There’s also a programmability branch, with sub-branches for stored procedures, functions, rules and other objects.

The larger, central window can be used to preview objects that have been repaired. For example, if you expand the table branch and click on a table, you can see the contents of that table.

This is pretty handy, especially, for making sure your file has repaired successfully and accessing specific business-critical data more quickly. You can expand the menu below any table and review the table columns, primary and foreign keys, constraints, indexes and other information.

There’s also a search field to find tree items, especially helpful with large databases.

When you’re satisfied the results are what you wanted, click “save” to complete the repair. As mentioned, you don’t have to save the repaired file as a database file; there are options for .csv, .html and .xls, too.

If creating a new database file, you’ll be given options to create a new database or save to a live database.

To create a new database, click the “existing database” radio button and enter the server/instance name, select a destination and click “connect” to complete the process. For existing database recovery, click the “live database” radio button, enter the server/instance name and then select the database using the drop-down menu. Click “connect.”

In either case, the client will let you know that the file has been saved in the desired location.

If you run into trouble during the repair process, you can access a manual and FAQ feature directly from within the client, without having to head to Stellar Data Recovery’s online knowledgebase. Click the “?” icon to do either.

Final Thoughts

The goal of this quick guide was to show you how easy SQL database repair can be with good third-party software. We picked Stellar SQL Database Repair as an example owing to its simplicity, features and appropriate costs relative to similar software. You can test the software out yourself by downloading a free trial, of course, to evaluate it for yourself.

If you’re as pleased with the software as we were, don’t forget to check out our review of Stellar Data Recovery, which can be used to resurrect deleted and corrupted files on your hard drive. Stellar has a range of other products, too, a few of which we’ve featured in similar guides to this one:

• How to Convert Outlook OST Files to PST
• How to Convert Exchange EDB Files to PST
• How to Recover Microsoft Exchange Mailbox
• How to Repair Outlook Database Files

Feel free to ask us any questions about repairing database files or the Stellar software, below, and we’ll try and find answers for you. Thanks for reading!