Performing a live row-level data restore

The situation

A poorly executed query has resulted in damaged and missing data in a known area of your database. The data is not immediately critical to the customer-facing live website but detaching the database would result in hours of lost customer data input. You need to restore the damaged data as quickly as possible but without taking the production server offline.

Key challenges

• Identifying the precise location of missing or erroneous data on a live server.
• Restoring the missing or damaged data in the shortest possible time.
• Ensuring that the database stays live for continued customer data collection. 

How we can solve it

1. Use SQL Data Compare Pro to compare the live database with the last good backup.
2. Use the fine-grain navigation and selection features to isolate just the data that required restoring. Create a report of the differences if required for audit or version control purposes.
3. Run the synchronization wizard to generate a SQL script for restoring the last good version of the data from backup.
4. Run the script against a test database and compare again to backup to ensure a perfect match.
5. Synchronize the test server to the live production database.

To learn more, take a look at our worked example.

Benefits of our solution

• Comparing to Backup means you can restore damaged data direct without the need to restore the whole backup to another live server.
• Fine-grain navigation of the differences pane, including 'Find' box and 'pivot view' helps identify problem rows quickly, even in very large databases
• Re-use SQL scripts if, for example, same problem is replicated across a number of subscriber databases.
• Reports and scripts provide an audit trail.

Support & documentation

• SQL Data Compare