This is Part 5 of an independent review of a product, Stellar Repair for MS SQL, from the folks at Stellar Info.

The version being reviewed is 11.0.0.1

In our previous post, “Product Review - Stellar Repair for MS SQL - Part 4: SQL Server Database Recovery”, we looked at how to recover data from a database file (.MDF).

In this post, we will discuss corruption.

Throughout the series, you will no doubt have been curious about the fact that I have demonstrated the retrieval of data and objects from an .MDF and a .BAK that were fully functional.

You might have wondered what the point was, given the review is for a tool that helps you with recovery.

At this point, we need to define corruption.

And to define that, we need to look at the structure of the various files.

Database

A SQL Server database is composed of the following:

  1. At least one primary data file, .MDF. This contains database metadata and pointers to the other files in the database.
  2. At least one transaction log, .LDF. This contains information for database recovery.
  3. Optional secondary data file, .NDF.

The primary data files (.MDF and .NDF) are logically divided into pages, and operations in the database are performed at the page level.

Backup

Database backups are stored in the .BAK format, which is a format based on the Microsoft Tape Format (MTF).

When it comes to corruption, we can mean one (or more of the following)

  1. Loss of data - either the headers or the file data itself via various options - physical disk damage, truncation of data or read/write errors
  2. Flipping of data - again, bytes in the file get flipped for myriad reasons, such as physical disk damage, truncation of data, or read/write errors
  3. File system corruption during the reading or writing of data

At this juncture, I must share an anecdote of an issue that I ran into many years ago.

The database server had files on a FAT32 partition.

In case you did not know, FAT32 had a maximum file size of 4GB.

You can see where this is going.

The backup files .BAK for this database were generated and saved correctly. The backup sizes, however, continued to grow until the hard limit was reached, after which the backup files were truncated at 4 GB. As this was taking place using a command utility at 2 AM, there was nobody around to review the error messages.

SQL Server itself comes with tools to detect damage and attempt to repair databases - DBCC.

Details of how to use this tool can be found here.

This is the space in which Stellar Repair is playing.

The reality of the matter is that the ability to recover data from either the backup (.BAK) or the data files (.MDF) depends entirely on:

  1. How much of the file is available
  2. How much data is corrupted
  3. How much data is missing

At this point, I must point out that any responsible operations procedure must do the following:

  1. Regularly back up the database, and regular here has context - daily at the very least.
  2. Verify that the backup is functional, preferably by immediately restoring the backup to a different environment with a similar configuration.
  3. Store backups in at least 3 different locations, one of which must be entirely offsite
  4. Have alerting mechanisms in case anything fails.

TLDR

The ability to recover a database from a file or a backup will depend entirely on the nature and extent of the damage and/or corruption.

Happy hacking!