The year 2007 has been a time of many changes for SQL Data Compare 6. We also hope that Row Level Restore, one of SQL Data Compare Pro's new features in version 6, will radically change the way DBAs go about restoring data to databases.

After all, restoring data has, until now, been a teeth-grindingly painful process. It can take valuable time to restore several backups until that original, greatly sought-after data is finally located. Not only that, but every time a backup is restored, the file has to be copied to a SQL Server. The database then has to be restored, and the data carefully examined with SQL Server Management Studio or SQL Query Analyzer, to track down the original data. Once the original data has been located, the SQL to restore the data then needs to be written by hand and tested. Once this process is complete, the SQL can, at last, be applied to the live SQL Server. This process can take still longer if the backups are large, or if there isn't a SQL Server available to which the test databases can be restored. Not exactly an experience to relish, last thing on a Friday afternoon!

Fig 1. The conventional way to restore data in a minimum of six different steps

Table-level restore isn't a wholly satisfactory solution to this problem, either. Sure, with table-level restore, you can restore the entire contents of a table, but careful and cautious DBAs will always need to comb through the object being restored, to check that there are no dependencies between the data in the object being restored and data in other tables. This is in case data in other tables has changed between the present and the time when the object being restored was last available. You just can't take it for granted that table-level restore won't disturb the referential integrity of your databases. Of course, this can be worked around by manually scripting each object that has a dependency. This still takes up time, though, so that any time saved with the restore of a single table, rather than a database, could soon be lost.

With these agonizing processes in mind, we at Red Gate have been working away over the summer months (at least we didn't miss much sunshine this year) to develop a faster, easier, and more targeted way for DBAs to perform data restores. Now, with the release of Row Level Restore in SQL Data Compare Pro 6, we think we've cracked it.

So, what does Row Level Restore mean for the DBA, and why do we think Row Level Restore is better than table-level or object-level restore? Well, in common scenarios such as viewing data for auditing purposes or trying to restore corrupted data, it's often the case that only a single column or a few rows of data need to be restored to solve the problem. So why spend time restoring all your data when you're only after a few bytes? In contrast to table-level or object-level restore, Row Level Restore means the DBA has extra granularity to home in on the data that is actually required. A mature UI has also been developed in SQL Data Compare Pro to make this process of drilling down and viewing data easy.

 

Fig 2. Restoring data with SQL Data Compare Pro

The ability to restore data at row level means that DBAs no longer need to go through a minimum of six lengthy, and often manual, processes to restore data. With Row Level Restore, DBAs can compare backups directly and view the changes to the data in a mature visual interface. SQL Data Compare Pro can then be used to generate and run the required SQL to synchronize the databases.

There are, of course, a few limitations when comparing to backups, such as the fact that only tables with a matching primary key, unique index, or unique constraint, can be compared. Non-persisted computed columns and views cannot be compared, either. However, where it is possible in most cases to compare to and against backup files, Row Level Restore greatly reduces the time spent manually comparing, writing, and executing scripts between backups and servers.

So, how do all these savings in time and effort come together in SQL Data Compare Pro 6 for DBAs?

Firstly, DBAs can compare backup files either from SQL Backup (version 3 or later) or from native SQL Server to a live SQL Server database. Backup files can even be compared directly with one another. There is no need to have SQL Server installed in any of these cases, as SQL Data Compare can work with backup files from a remote location. Nor is there the need to copy the file to a local directory, as you can use the file directly from your network storage. You can even drill down to choose which tables you wish to compare, or restrict the number of columns, to further speed up the whole process.

Secondly, as SQL Data Compare compares data, it behaves just as if the database is a live backup. DBAs then have all the standard features in SQL Data Compare to visualize data, search for changes, and choose which tables, columns, rows, or even individual fields they wish to synchronize.

Finally, the Synchronization wizard can output the SQL code for all the changes between the tables being compared, or just the SQL for the table being viewed. SQL Data Compare Pro can then be set to run the SQL on the appropriate server.

The overwhelming reaction so far from all those who have tried Row Level Restore has also confirmed how much time is saved over all the manual processes. One of my favorite pieces of feedback came from a happy tester of the Alpha version. Before trying out SQL Data Compare Pro 6, the tester would restore the customer backup, then take hours comparing the specific tables and views with the UNION ALL operation. SQL Data Compare Pro 6 managed this process for the tester in a few minutes. Through all the work that we have put into this feature over the past year, it's a satisfying feeling to know that SQL Data Compare Pro 6 and Row Level Restore could, as the tester himself expressed, be the "answer to prayers."

 

Author profile:
	Richard Mitchell is Development Manager of SQL Data Compare 6, and has also worked with ANTS Profiler™ and developed the Red Gate website and intranet. In his spare time he scares partridges riding his motorbike around the fens, and decorates his new house.

 

Read what our customers have to say about Row Level Restore.