Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Review - MSSQLRecovery

By Dale Elizabeth Corey,

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

Operating systems = Windows 95, 98, ME, XP, or 2000 SQL Server Versions = Microsoft SQL Server 6.5, 7.0, and 2000
Hard Disk = 5 MB minimum of free space Screen Resolution = 640 x 480 and up, 256 colors

RAM = 64 MB for Windows 95, 98 or ME; 128 MB for Windows XP or 2000

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.


Screen 1


Screen 2


Screen 3


Screen 4


Screen 5

Click here to see a partial sample of the actual log file that was created.
Note: Registry entries were not included in the sample.


Screen 6

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.


Figure 1


Figure 2

Figure 3


Figure 4


Figure 5


Figure 6

Support

OfficeRecovery customer support can be contacted through the following methods (Online Customer Support is recommended).

Online:

Online Customer Support

Email:

support@officerecovery.com

Fax:

+1 (650) 989-6714

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."


Figure 7

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.

Ease of Use 5 Very easy.
Feature Set 5 This product serves a very specific purpose.
Value 5 It can save your hide!
Technical Support 4 Online oriented with brief interaction.
Lack of Bugs 4.5 See Conclusion section.
Documentation 4 See Conclusion section.
Performance 4.7 Works great. For larger files, beware.
Installation 5 Great!
Learning Curve 4.5 You are on your own after the recovery.
Overall 4.6

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

Total article views: 6710 | Views in the last 30 days: 1
 
Related Articles
FORUM

if database crashes how to recover in sql server 2008

if database crashes how to recover in sql server 2008

FORUM

'Recovering' state of database

Multiple databases switch between "recovering" and normal mode

FORUM

How to recover Database from Suspect mode

How to recover Database from Suspect mode

FORUM

Recover database on missing Tlog's

Recover database on missing Tlog's

FORUM

Recover database with only .LDF file

How to recover a database with .Ldf file.

Tags
product reviews    
reviews    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones