10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

A SQL database is only as good as its backups. But when disaster strikes and no backups are available, what do you do? Is there any hope of salvaging the data stored in that critical SQL server? The answer can be yes - even without a backup.

If you catch the problem early enough, MS SQL Server has the ability to "reset" a corrupt database using DBCC CHECKDB. This checksum utility can be run against an entire database or an individual table. This is all well and good for smaller databases, but what about large databases? Restoring even 10GB of data can take hours or days - time that most businesses don't have in times of crisis. With larger MS SQL Databases this strategy simply isn't feasible. But is there another way...?

Fast Database Recovery for MS SQL Servers Fast Database Recovery (FDR)  is one tool that can save the day (and possibly your job!) when faced with a damaged MS SQL Server. FORC  (FDR's Recovery Console) can be run to scan the contents of your SQL database and present you with a list of recoverable items. You can selectively restore these items back into an empty database or attach to an existing one.

Here is how it works...

Start FDR -

The process is initiated by running it (the installer will automatically copy this file for you). Once started, FORC selects the last known good backup for your server and begins scanning its content. Note: By default, it will use your MDF files as a base reference point, but you may change this in the "Input" tab in the FDR Options menu if required.

This action takes anywhere from a few minutes to half an hour depending on the size of your database. In our example below it took about 3 1/2 minutes as I am using a small 200MB database - FDR can be run against much larger databases however, even those in the tens or hundreds of gigabytes!

In addition to scanning the content for recoverable objects, FORC will also check the header integrity of these items. If any header errors are detected, FDR will suggest a course of action based upon whether you have other copies available:

A good candidate for recovery

A candidate only if no other "known good" instance exists Once all database content has been checked and repaired (if required), FDR displays its findings. This includes all recoverable items, their location, whether they are good or corrupt, and additional options that may help the recovery process.

Note: FDR will not restore "live" database files - it can only apply database backups! The next step is to either attach the damaged database file(s) or extract individual pieces of data from them. FDR makes this easy by providing VBA scripts to extract data directly into Excel spreadsheets for further analysis. You can also use these same scripts to extract specific objects within a database for direct import into other databases/applications if required.

Using FDR in Transition

The biggest advantage of FDR over using SQL server built-in check DB command is that it does NOT require SQL Server to be running. It can run completely independent of SQL Server! This is very important when you are in Disaster Recovery mode - when the entire Database server may be offline due to network or power issues, but you may need to recover some objects immediately. Most of the time this action would not even be possible if the main database was a 'write-only' state and unavailable for check DB commands!

FDR vs Other Solutions

There are other 3rd party software applications that claim to repair damaged MS SQL databases. However, unlike FDR, these require a functional MDF file which must be present on the physical disk(s) containing data from within it. In addition, they also require SQL Server to be up and running in order to perform their operations.

With FDR you can extract single objects from a damaged MDF file without having to restore the entire database content first. So in conclusion, when faced with a damaged MS SQL server there is no need to panic - FDR has you covered! If interested, simply visit its homepage for more information.

Articles published on SQLKoda are not intended as an invitation for software piracy nor any kind of endorsement of commercial software - it reflects my personal experiences during evaluations and deployments I've been involved with.

Please consult your local government and business authorities before attempting any such actions and ensure that all products deployed comply with licenses and/or other relevant regulations including updates and validations where appropriate, prior to use or installation.

The site contents (e.g. text, images, and code samples) are protected by copyright law and ownership remains with the site owner at all times - please see my terms of use for more information.

Conclusion:

In this article, I covered the use of a freeware application called FDR which can help you recover corrupt or damaged MS SQL databases. This may take some time to complete, but for me, it's definitely worth the effort when there is no other way out. FDR is available for download.