Introduction

Have you ever had that sinking feeling in the pit of your stomach that you just corrupted the database and you know the boss is going to kill you 'cause you have this urgent deadline? That is the type of feeling you get when your blood drains from the top of your head all the way to your toes. Well, maybe MSSQLRecovery can help you keep your job! MSSQLRecovery will allow you to recover SQL server databases (.mdf and .dat) and backups (.bak) With MSSQLRecovery you can restore multi-volume database files, tables, stored procedures (even encrypted ones), views, indexes (includes IGNORE_DUP_KEY, CLUSTERED, STATISTICS_NORECOMPUTE, and FILLFACTOR), primary/unique/foreign keys, and triggers. Additionally, it can recover predefined defaults, default values, rules, constraints, and user data types. MSSQLRecovery can also recover deleted records (saved as a separate SQL script) and partly damaged records (stored separately in a batch file).

MSSQLRecovery creates script files that will recover the database structure and data. The rebuilding batch is generated automatically. The rebuilding scripts are divided into multiple parts for convenience.

However, you need to be aware that MSSQLRecovery cannot do everything to repair your databases. Sort orders for index keys are only supported for SQL Server 2000, timestamp and sql_variant field types are not supported, dates that are before January 1, 1900 and after December 31, 2199 may not be recovered accurately, the original row order may be different, deleted records can only be recovered for SQL Server 7.0 and 2000, and BLOBs larger then 10Kb can not recovered for SQL Server 6.5. Also, When parts of the database file become overwritten (due to file allocation/bad sector problems), you can expect that the amount of data recovered will only be minimal. Most importantly, expect that you will have some work to do after the recovery.

Environment

Installation

The installation (see sample screen shots 1 - 6 below) was very straight

forward and easy. I appreciated the Diagnostics option which allows

you to submit a zip file (be sure you stick to an 8 character filename)

containing a log of system information and registry settings to support@officerecovery.com

in case there are installation issues. I wish more vendors would provide

that.

Using MSSQLRecovery 2.0

• To recover a corrupted SQL Server database you need

  to stop the MS SQL Server service and then make a copy or backup the

  corrupted file(s) to non-local media such as a CD, another hard drive, a

  network drive, a tape drive, etc.

• Start MSSQLRecovery (see Figure 1 below) and click the Recover button on the toolbar.To perform the same action, you can also select File => Recover... item from the menu or you can key CTRL+R. Select the file to recover in the browser and then click the Recover button (see Figure 2 below).

• Select the folder to save the scripts (see Figure 3). By default, MSSQLRecovery will create a new folder (named "Recovered "+[database name]+" "+GETDATE()) for the SQL scripts to run on the MSSQL Server (see Figures 4 and 5). MSSQLRecovery uses the following file naming conventions:

  schema.sql

  dataNNNN.sql (where NNNN is a consecutive number (e.g. 0001,

  0002, etc.)
  deleted.sql (for deleted records)
  corrupted.sql

  (for corrupted records)

• Start MS SQL

  Server and run the scripts. To make the import procedure more convenient for the end-user, a batch file named "commit.bat" is automatically created (See Figure 6). Click here to pull up a zip file with all the scripts.

Other Things to Note

• A separate file called corrupted.sql is created during the recovery process. It contains records with field(s) that are lost or damaged due to data corruption. Automatically inserting these records into the recovered database might lead to loss of data integrity. It is recommended that you review and manually add these records to the recovered database.

• The TEMP environment variable should point to a folder located on a NTFS drive partition. If the database file is larger than 4GB, it can only be recovered on a NTFS partition.

• If .NDF files are present, data is recovered from them using the database structure in the .MDF file.

Support

OfficeRecovery

customer support can be contacted through the following methods (Online

Customer Support is recommended).

OfficeRecovery recommends that you create a zipped log file of the software

activity by holding down the Shift key while

selecting Recover… item in the main menu or by pressing the Recover button

on the toolbar.

Conclusions

This is a great product for recovering corrupted databases. It is

super easy and extremely efficient. The sole purpose of this product is

to save as much structure and data from your corrupted database as

possible. It is a MUST for any database administrator.

OfficeRecovery recommends that if you want to find out if a file is recoverable,

try the

MSSQLRecovery demo first and then you can decide if you want the full version.

The only

peeves that I had with that software was that the link for the Help file on

the Windows menu for MSSQLRecovery did not work (see Figure 7 below), and the

batch file, called "commit.bat", that MSSQLRecovery creates was not

very well documented. In fact, the batch file was never mentioned by name. However, this tip was noted in the documentation:

"The other alternative to import data into the newly

created database is to process the SQL scripts recovered with the isql.exe

utility that is included into SQL Server installation package."

Ratings

I will rate each of the following using a scale from 1 to 5. 5 being the

best and 1 being the worst. Comments are in the last column.

Product Information

Web Site: http://www.officerecovery.com/mssql/index.htm

Developer: OfficeRecovery.com

Pricing:

Single User - US$499

Server license - US$599

10 machine license - US$748