Worked example: restoring from a backup file - SQL Data Compare

This worked example demonstrates a table-level restore from a backup file. You can also restore specific rows.

In the example, the Magic Widget Company has a SQL Server database running on a test web server. The Magic Widget Company's test team has been testing the new version of the web site.

One of the software testers has updated the Contacts table, intending to update one email address. They did not specify a WHERE clause, and consequently have updated the entire table. During the day some other rows in the Contacts table have been modified. The database administrator has been asked to restore the data from a backup and apply some but not all of the changes that were made to the test server.

You can follow the example on your own system, if you are using the SQL Data Compare Professional edition. You will need access to a SQL Server to do this.

If you have not already followed the Comparing and synchronizing two databases worked example, you are recommended to do so before starting this worked example.

This example has three steps:

1. Set up the comparison

   Create the example databases, and specify the data sources you want to compare.

2. Select rows to restore

   Review the results and select the rows you want to restore.

3. Synchronize the data sources

   Create and run a synchronization script.

The worked example uses the following data sources:

• WidgetTest is the test database
• WidgetLive is the database used to create the backup
• BeforeEmailUpdate.bak is the backup file

Set up the comparison

To improve performance, and make the comparison results easier to interpret, in this example we will compare only a single column in a single table. To do this, we will specify the data sources, then the table, then the column. No other values are included in the comparison.

Specify data sources

1. To create the databases, paste the SQL creation script into your SQL editor, then run it.

   The databases are created and populated with data.

2. To create the backup file, run the command:

   BACKUP DATABASE WidgetLive
TO DISK = 'c:\BeforeEmailUpdate.bak'
WITH INIT,
NAME='BeforeEmailUpdate'

   The backup is created at the root of the C drive on the computer running the instance of SQL Server you are connected to.

3. Start SQL Data Compare if it is not already running. The Project Configuration dialog box is displayed showing your most recent project. You can edit the current project, or create a new project.

   If you want to create a new project, click Cancel to close the dialog box, and on the toolbar click (New Project)

   If you have any existing projects, and the Projects dialog box is displayed instead, click New Project.

4. On the Project Configuration dialog box, under Source, select Backup:

   

5. Click Add Backup Set Files to specify the backup.

   The Add Backup set Files dialog box is displayed.

6. Select the file BeforeEmailUpdate.bak, and click Open.

   The BeforeEmailUpdate backup set is displayed in the list of backup sets.

7. Under Target, select Database.
8. In the Server box, type or select the name of the SQL Server where you created the sample databases.
9. Type or select WidgetTest in the Database box.

   If the database is not displayed in the Database list, right-click the Database box and click Refresh, or scroll to the top of the list and click Refresh.

10. On the Options tab, click Red Gate Defaults, to ensure the default options are being used.
11. Click the Tables & Views tab.

    

    The Tables & Views tab enables you to select the tables you want to compare and which columns you want to compare.

    By selecting only the tables that you want to compare, you will improve the performance of SQL Data Compare, as it does not need to read the entire backup file. This is useful for large backup files.

    Note that if you are using a backup as a data source, you cannot compare views.

12. Click None, then select the check box for the Contacts table.

    Only Contacts is included in the comparison.

13. Click in the Columns in Comparison box for the Contacts table, and on the dialog box, clear the check boxes for all columns except Email, then click Close.

    Alternatively, you can right click, click Select None, then select the check box for the Email column.

    

    Only 2 out of the 9 columns in the Contacts table will be compared; the ID column (the comparison key) and the Email column.

14. Click Compare Now.

SQL Data Compare displays a message dialog box that shows the progress of the comparison. If you select the Close dialog box on completion check box, SQL Data Compare closes this message dialog box automatically the next time that you run a comparison on a project. For this example, leave the setting as it is, and click OK to close the message box.

Select rows to restore

The results of the comparison are displayed in the upper (Results) pane:

To view the comparison results, click or click the object group bar:

The upper pane also shows how many rows of each type exist. There are 2 rows that exist in the backup but not in the WidgetTest database, 98 rows that exist in both the backup and the WidgetTest database but have different values, and 2 rows that exist in the WidgetTest database but not in the backup.

In the upper pane, click Contacts to display detailed information about the rows:

ID is the comparison key . Email values that are different are displayed with a dark shaded background; Email values that do not exist in one data source but do exist in the other are displayed with a shaded, patterned background.

If you scroll through the rows, you can see that all the Email values have been set to Matt.Mitchell@example.com except for the two rows that do not exist in the WidgetTest database and the two rows that do not exist in the backup.

As restoring all rows would over-write those that exist only in the target, we must exclude these rows from the synchronization.

In the upper pane, clear the Target Only check box:

100 of 102 rows are now selected for synchronization.

To restore specific rows, you can use the Include check boxes in the lower (Row Differences) pane to select the rows you want to synchronize. Alternatively, you can filter the comparison with a WHERE clause.

In this example, we will synchronize the remaining 100 rows.

Click to run the Synchronization Wizard.

4. Synchronize the databases

On the first page of the Synchronization Wizard you can choose to create and save a synchronization script, or perform the synchronization using SQL Data Compare. In this example, we will synchronize using SQL Data Compare, and perform a backup before synchronization.

Choose synchronization method

1. Ensure that Synchronize using SQL Data Compare is selected.
2. Ensure that Back up target before synchronization is selected.
3. Ensure that Save a copy of the synchronization script is selected.
4. Click Next

Configure backup

1. In the Backup using check box, select Native SQL Server

   If you have installed SQL Backup on the SQL Server, you can select Red Gate SQL Backup instead.

   For more information, see Backing up before synchronization

2. In the Backup folder box, specify the backup location, and in the box on the right hand side, type a file name, for example WidgetTestBackup
3. Click Next

Review

There are three tabs on the Review page:

• Synchronization script shows the script to synchronize the data sources.

  You can search the script, save it, or copy it to the clipboard.

• Summary shows a synopsis of the actions in the synchronization script.

  You can view the summary grouped by the objects affected, by the type of modification, or by the order in which the script modifies the target.

• Warnings shows a list of any warnings about unexpected behavior that may occur when you synchronize the databases.

For more information, see Warnings

You can choose to run the SQL script from within SQL Data Compare, launch your SQL editor so that you can modify the script, or save the SQL script without synchronizing the databases.

In this example, we will choose to run the script, then compare the databases again to check the results, and save a copy of the synchronization SQL script (the default)

1. Click Synchronize Now to run the synchronization.
2. A confirmation dialog box is displayed. Click Synchronize Now to continue.

   The backup is created, the synchronization is performed, then the data sources are compared.

   For this example, leave the setting as it is, and click OK to close the message box.

   The comparison results show that the Email values for rows that existed in both the backup and the WidgetTest database have been synchronized: