Compare To Backup in SQL Compare

SQL Compare 7 was an exciting release for us; not only did we manage to add full support for SQL Server 2008 (before Microsoft released it !) and fix loads of bugs, we managed to find enough time to implement Compare to Backup functionality. Compare is now able to transparently handle both native (SQL Server 2000, 2005 and 2008) backups and SQL Backup backups including all of Microsoft’s latest features such as native compression, filestreams (version 7.1) and row and page compression.

However, those of you who are Red Gate customers will have realised that Compare is basically only gaining functionality that Data Compare has had since version 6. So if you haven’t needed Compare to Backup by now why might it be useful for your job? Well, Compare to Backup allows you to recover lost schema without wasting the time and space necessary for a full restore.

Since schema information is only a small part of the average backup, Compare to Backup is largely able to avoid loading the data portions of the file, allowing object-level schema restoration to be done quickly and easily.

So with Compare to Backup, and Data Compare to Backup, backup files are as flexible as live databases, and a vast array of partial restores of both schema and data are possible.

The Backup Reader is composed of a number of different layers of abstraction which are designed to divide the process into a number of logical units that provide flexibility in design as well as making the whole code base more maintainable.  The logical units are listed below working up from the native file to SQL Compare.

  • Raw File Reader  – Provides a resilient reader of the Raw Data File.
  • Decompression Unit – Decompresses SQB / Natively compressed files transparently.
  • MTF Reader – Interprets the generic MTF (Microsoft Tape Format) File, which is a common superset for output to (tape) drives by Microsoft systems.
  • Page Reader – Converts the data segments in the MTF file into Pages accessible by the page/file id (output is the byte array shown in dbcc page(*)).
  • Table Reader – Reads system tables and works out how to assign the bytes of a page to the cells of a table.
  • SQLite Loader – Loads the System Tables into a SQLite in process database.
  • Compare To Backup Interface – Presents the SQLite database in a manner suitable for SQL Compare to access.
  • Compare Population – The main SQL Compare population engine used for both live and backup databases.

Although many upgrades were made to the core Backup Reader code base to provide full
2008 support, the main component I will discuss is the Compare to Backup interface, since this was the primary feature that allows SQL Compare to take advantage of the Backup Reader technology.

It can be easily seen through an admin connection that there is a large discrepancy between the 2005/2008 System Views and the real System Tables which underlie them. The queries that SQL Compare uses to access live database information are in themselves complex, and when they are expressed in terms of the system table it soon becomes obvious that it would be infeasible to express them in terms of C#. As such we needed a SQL like interface that we could use to feed complex queries through and we decided to co-opt SQLite into the code base. This means that all the system tables are loaded straight out of the backup file into SQLite and then the reformulated SQL Compare queries are used to retrieve the necessary information. In some cases the reduced syntax of SQLite couldn’t handle the complexity of the query and some C# was used to provide the additional processing.

On top of the differences at the table level, a number of the system view columns were defined in terms of SQL Server functions, for example the method to obtain the text of an XML schema collection is through a function, and similarly the different fields of a certificate or the identity information of a column are retrieved through other functions.

The most difficult function to replicate in code was that for XML schema collections, as the system table representation (sys.sysxmlcomponent, sys.sysxmlfacet etc) is heavily parsed and compressed for efficiency reasons. To return it to its original textual form required a lot of logic to reverse all the original compressions and to reconstruct the XML’s tree-like form.

Although we could have provided all the functionality of SQLite in-house it is quite likely that without it the Backup Reader project would never have been completed due to the time that would have been required to develop this alternative solution. As a technology we found that it largely met our needs, was stable and most importantly was quick and easy to use.

The ‘Compare To Backup’ functionality is probably not for everyone but hopefully there are enough people who will find it useful to have made the whole project worthwhile. Please try it out, and let us know whether it is useful to you.