Data Backup Digest

Whether it’s by a specific time, transaction or application, a certain level of consistency is required in order to ensure the viability and effectiveness of a data backup. Without achieving at least one of these factors, a backup may be rendered completely inaccessible, useless or only partially available. This is what is known as a “fuzzy” backup, and it occurs when a dataset’s state is changed or modified during the backup process.

In order to achieve one of the three factors of consistency, most computer users are able to initiate a point-in-time (PiT) copy, which prevents your data from changing or updating during the backup process. However, certain applications, especially those which must run nonstop, cannot be updated in this fashion without stopping the application. In some scenarios, this simply isn’t a viable solution.

This is exactly where the concept of the fuzzy backup comes into play. While it’s certainly not a foolproof solution, a fuzzy backup is able to copy data regardless of whether or not it is currently being modified. While this approach is not ideal, it may actually be the best option in some cases.

Users who end up with a fuzzy data backup can still attempt to recover their information, but their results will be anything but consistent. While some users do report successful restorations from fuzzy backups, most are unable to restore their data. In fact, most users who attempt a restoration from a fuzzy backup tend to report one of three cases.

Either their data is perfectly fine, useable and accessible, which usually happens by accident and is most likely non-repeatable, or their data is inconsistent. If it’s the latter, the restoration process may signify data errors or ABENDs, also known as abnormal ends. In some cases however, no errors are reported – even with data that is inconsistent or inaccessible.

Fuzzy Backups in SQL Server

Both Microsoft SQL Server 2000 and SQL Server 7.0 utilize a fuzzy backup protocol in order to enhance and streamline the entire backup and restoration process. Data contained within a series of extents are then written, which is accomplished without the need to synchronize pages that are currently in use. As such, the overall time required to complete the backup is reduced drastically. Moreover, SQL Server’s fuzzy backup protocol copies pages serially, thereby eliminating randomness within the backup process. The result, however, is backup inconsistency.

In order to counter the problem of inconsistency with their internal fuzzy backups, SQL Server utilizes the RESTORE statement to create a new database. Restorable data contained within the extents that were archived earlier are then integrated into the new database. Because the extents are stored serially, the restoration process is faster than other methods. Moreover, the restoration process skips over and extents that are missing from the backup set, thereby saving even more time.

The final step of the restoration phase involves recovery of the database itself, which is initialized through a transaction log that was archived earlier in the process. Once complete, the database will now be in a consistent and recoverable state.