Version 7 of the Red Gate SQL Comparison SDK has finally brought the possibility of recovering individual objects from a Microsoft SQL Server backup to a live database. I found this an exciting opportunity to flex the old noggin and design a program that can restore schema and associated data from a backup file to a real database.

The reasons why you would want to do this are vaired. For example, I've heard it would be useful for database normalization, inserting test data, and some obscure disaster recovery situations. Of course, the compelling reason for me is "because I can"!

Simply restoring a table and its' data alone could have unintended consequences. For example, what happens when foreign keys exist between the table you want to restore and other tables? What if the table has dependent objects, such as a default bound to a user-defined function? The SQL Compare Engine can handle all of these things because of its' dependency engine which can create all dependencies in the proper order. The Data Compare Engine can also handle foreign key relationships, triggers, and unique constraints.

This means that you could insert a stored procedure from a backup, and the API can add the tables and other objects that the procedure needs, or even adjust the existing schema so that the stored procedure will run without errors!

The example program can do this and more: if the schema object being recovered is a table, all existing data will be altered, superfluous data deleted, and new data inserted so the live database will exactly match the data in the backup. In this example, the table must have a primary key or unique index to match rows of data together. It would be possible to add extra logic to allow the user to pick one or more columns to act as the basis for a comparison key.

The only downside to combining schema and data recovery from backup using the SDK is that, because the SQL Compare Engine is optimized to retrieve schema and the Data Compare Engine is optimized to retrieve data, it is necessary to read the backup twice in order to retrieve schema and data. Hopefully Red Gate can combine the two engines in the future to make the process more efficient!

The example C# project can be downloaded from the Red Gate Labs site: http://labs.red-gate.com/uploads/5/54/SQL_Object-Level_Restore.zip

You will also need to install the assemblies from SQL Compare and SQL Data Compare by downloading the SQL Comparison SDK.