Introduction

It is always uncertain when an instance of SQL database corruption strikes mutely and takes a toll on your performance and database availability. There are several aspects of database corruption and each of those strives to bring a lot of damage to your production. Stellar Phoenix SQL Database Recovery comes in handy at the right time when nothing else works. This piece of software makes it easy to recoup everything in your SQL database that has suffered from any kind of severe or minor corruption. A new upgraded version is added to the lineup each year and the recent version 4.1 is more than adequate to meet the recovery needs of all spectrums of database users.

Environment

I was provided with the full version of the Stellar Phoenix SQL Database Recovery by the company that makes the software, i.e. Stellar Data Recovery. They wanted me to write an unbiased review of the product and dive deep to explore the nuts and bolts of the tool.

I decided to test the tool against different types of database corruption and know how well the software works in these times of crisis. The test setup consisted of a Windows 8 PC with SQL Server 2008 R2 (Version 10.50.4000) preinstalled. All the tests were carried out on an SQL database to which the corruption was induced manually. In order to check for data corruption, a single table was used that had the same level of corruption as other tables in the database.

Installation

Installing the software was an easy accomplishment. I ran the executable ('StellarPhoenixSQLRecovery.exe') file given by the company and the overall process was smooth. Just had carry through a few easy instructions to install the software at the desired location. The wizard-like interface made installation a fun and you could also choose additional features in the 'Select Additional Task' dialog according to your requirement.

Different Database Corruption Types

Before acquainting you with the test results, I would like to give a brief on different types of database corruption:

Metadata Corruption

You might have heard that SQL Server 2000 made it quite easy to get updates to system tables as and when the need strikes. You can make any desired changes to the three critical system tables: sysindexes, sysobjects, and syscolumns. With this flexibility, many times you ended up making these tables inconsistent. For instance, you may delete an object from 'sys.objects' and miss out updating the other two tables. DBCC CHECKDB will report metadata corruption errors when you upgrade to SQL 2005 or higher version. This is because DBCC CHECKDB utility in higher versions includes DBCC CHECKCATALOG checks.

Page-level Corruption

A SQL database can be seen as a set of individual pages residing on the disk. These pages hold different pieces of information that are critical to maintaining consistency of the database. If any damage occurs to these pages, you need to do page-level restores. You can restore the faulty page from a good backup and check if the corruption has been resolved. In such cases, you also need to back up the current log and restore in the current database.

Clustered and Non-Clustered Index Corruption

Non-clustered indexes are representative of redundant data in your database. With this type of corruption, you do not need to take the actual data offline or get your database into single user mode. If it is just the non-clustered index corruption, you can go for an offline index rebuild and run DBCC CHECKDB afterwards to check database consistency.

When it comes to clustered indexes, they form your base data. If the corruption hasn't spread all over, you can do page-level restores to fix the issue. However, if the corruption is wide spread, you need to go for a full restore. If the backup is missing here, you will end up losing data.

Database Table Corruption

SQL Server administrators come across situations where the corruption is left unidentified for long. It only becomes visible when you scan a table and query for some data from a specific record. In these cases, administrators opt for a physical consistency check of the database to see the extent to which the damage has occurred. The output of DBCC CHECKDB may include several recommended repair options for your corrupt database. This is the minimum level of repair required to fix the problem. If you see 'REPAIR_ALLOW_DATA_LOSS' as the minimum repair option, you are likely to encounter data loss.

MDF File Corruption

This is the most common corruption problem that concerns every SQL database user. A majority of these cases result from a severe server crash or hardware failure. MDF files are primary database files containing entire data and schema information. If this file becomes inaccessible, you fail to get your database online and face data loss.

Testing Stellar Phoenix SQL Database Recovery Under Different Corruption Scenarios:

Metadata Corruption

To test the software for metadata corruption, I picked up a database that was created in SQL Server 2000. After messing up the system tables by making random changes, I successfully attached the database to the running SQL Server 2008 instance on my Windows system. I found a recurring error in the SQL logs as shown below:

No catalog entry found for partition ID xxx in database yyy. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.

When DBCC CHECKDB was run against this database, the command terminated with an error 8930. This ensured that the database was having metadata corruption.

I quickly opened up Stellar Phoenix SQL Recovery tool to start scanning the software and recovering all the precious data. It took a few seconds for the tool to scan the database, as there was only a single table within. I was happy to know that the software did it just right and was able to read all the data off the corrupt database. I could see the table with its properties in the interface. Next, I headed to save the recovered file to my system. The filename was prepended with 'recovered_'. Stellar Phoenix SQL Database Recovery was able to do a pretty decent job for this test.

Non-Clustered Index Corruption

The test database contained only a single table that had both clustered and non-clustered indexes. Next, I fetched all the indexes on this table and noted the index_id of the non-clustered index. Collected all the page metadata of this index using 'DBCC IND' and randomly choose a page for corrupting it. The database was taken offline and corrupted using Hex Editor.

After getting that non-clustered index corrupt, I let the software do the recovery job. The software surprised me this time also, when I saw the entire table data absolutely intact in the interface after the scanning completed. With easy saving options, I was able to recover the database to a preferred location. The database was created from scratch by the tool, as the records from the original database were extracted and then inserted into a new MDF file. The results of this test were quite exemplary.

Clustered Index Corruption

I followed the same approach used above to corrupt the clustered index on that table. After corrupting the clustered index with Hex Editor, I brought the database online and ran DBCC CHECKDB only to find these error messages:

CHECKDB found 0 allocation errors and 4 consistency errors in table 'xxx.xxx' (object ID 1765581328).

CHECKDB found 0 allocation errors and 4 consistency errors in database 'yyy'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB

The 'repair_allow_data_loss' option confirmed that now the corruption was on clustered indexes. I gave Stellar Phoenix SQL Recovery a try and quickly loaded the tool with this database. This time the scanning process winded up showing a little less than expected. I could not see all the table records listed in the main interface. This was a partial recovery and some of the data was missing due to corruption on clustered indexes. The software allowed me to save whatever it could read in the corrupt database. Unfortunately, I should say that the tool is not a good choice for recovering databases having corrupt clustered indexes.

Page-Level Corruption

For this test, I corrupted the database by simply switching off the power from the mains supply when the database was in use. Ran DBCC CHECDB to ensure corruption and obtained the list of corrupt pages in the database. This was just a double-check. I tried to bring the database online in the instance and it easily did. Had it been corruption in FCB (File Control Block), this attempt would have gone in vain.

I ran the software against this database to scan the corrupt file. The software was able to read maximum information off the database. I could find only one or two records missing and the remainder of the file was recovered without a hitch. This was undoubtedly a win for Stellar Phoenix SQL Recovery.

Technical Support

I was guided by the Technical Support staff of Stellar Data Recovery throughout these testing procedures. I received a few mails from one of the support representatives that helped me resolve queries and reduce mistakes. Overall, the quality of service was phenomenal.

Conclusion

It seemed the database users could easily bank on this tool for resolving their everyday database corruption issues. The software managed to extract data in almost all corruption cases (leaving the non-clustered index corruption one). Stellar Phoenix SQL Database Recovery has an edge over other competing products in terms of scanning performance, ease-of-use, and flexible options to perform recovery. The tool is a good value for money given its out-of-the-box functionality and excellent technical support.

Ratings

Product Information: 

Site: http://www.stellarinfo.com/sql-recovery.htm

Information Guide: http://www.stellarinfo.com/installation-guide/sql-recovery/manual.pdf