SQL Compare 7 Beta and peeking into SQL Server backup files

(Editor’s note: the links to the beta are now deactivated)

I want to restore only a single table from my database backup. We have been hearing this request from many of our SQL Backup customers. The problem is simple: Given a large database backup and a live database that has a partial “corruption”, e.g. the data in a single table has been accidentally modified and committed, the DBA would like to have the table, and nothing else, restored. Or, another example is when a particular stored procedure has been modified, and we would like to get back the original version. This problem is a bit worse if we do not even know which backup file contains the right information.

In SQL Server the only way to solve the above problem is to restore the whole backup file to a new database, and copy the table data or the stored procedure to the current database.

Well, this problem is now history. We have just released the first public beta of SQL Compare 7. This tool, which is primarily used for comparing and synchronizing two database schemata, can now read the schema information directly from a backup file. This complements well SQL Data Compare 6.1 which already has this feature at the data level. These two products open up a lot of new recovery opportunities.

If you want to restore a few stored procedures, you just compare your live database with a backup file, and you can explore the differences in your schema objects. It may be that the modified stored procedure has a few dependent stored procedures that you also wish to restore. After selecting all the schema objects you wish to retrieve from the backup files, you can either restore them or create and save a synchronization script. When it comes to tables, Data Compare can do even more. Not only can you restore the data in a particular table, you can do this at a much finer level of granularity. First, you can see the differences between a table in the backup and the live database, and you can control which individual rows should be restored.

Anyway, back to SQL Compare 7 Beta 1. You can download it from http://www.red-gate.com/messageboard/viewtopic.php?t=6730 

It was quite a challenge to implement reading schemata information from backup files. First, the core system tables are different in SQL Server 2000, 2005 and 2008. Some objects, like XML schema collections, have very cryptic representations, and there is of course backup compression in SQL Server 2008. We are still working on row and page compressions. Of course we support Red Gate’s SQL Backup files, both compressed and encrypted ones, but the tool works with SQL Server’s native backups just as well.

Another major feature for this beta is support for SQL Server 2008. Although we will not see SQL Server 2008 RTMed until at least Q3 2008, when it is out, we hope to have SQL Compare 7 ready and waiting for it. In the meantime, you are encouraged to download and try this beta. Please let us know if you find any issues with it, or if you think that there is something we have missed from this product. The forum for this beta is on http://www.red-gate.com/messageboard/viewforum.php?f=78