Retirement information for the SQL Comparison Bundle.

• Red Gate Sql Compare Crack

This worked example demonstrates the use of SQL object creation scripts as a database schema when comparing and synchronizing databases.

Note that if you are using a 'check out/edit/check in' (VSS style) source control system, you can use SQL Changeset to integrate SQL Compare Professional Edition with your source control system. However, this worked example assumes you are not using SQL Changeset. For examples and details of how to use SQL Changeset, see SQL Changeset help.

In the example, the Super Sprocket Company has a SQL Server database running on a live server. This database contains a number of tables, views, stored procedures, and other database objects. The Super Sprocket Company's development team has been given the task of making a number of changes to the structure of the database, and updating the production server, while ensuring that both the new and previous versions of the database structure are stored as creation scripts in a source control system. A copy of the production database has already been restored to an empty database, ready for development.

You can follow the example on your own system, if you are using SQL 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.

Note that this worked example provides different databases for SQL Server 2000 and SQL Server 2005. The screenshots in this example use SQL Server 2005.

Setting up the databases

The worked example uses the following databases:

• SprocketProduction is the production database
• SprocketDevelopment is the modified version of the database containing the updates
• SprocketStaging is a copy of the production database used for development

To create these databases on your SQL Server:

1. If they already exist, delete the databases SprocketStaging, SprocketProduction, and SprocketDevelopment from your SQL Server.
2. For SQL Server 2000 users, click here to view the SQL creation script for the databases.

   For SQL Server 2005 users, click here to view the SQL creation script for the databases.

3. Copy the script, paste it in your SQL application, and then run it.

   The databases and their schema are created.

Exporting the database to a scripts folder

To save the database schema, export it as a set of SQL object creation scripts that you can later use to recreate the schema, or to compare with another database.

1. If you have not yet started SQL Compare, select it from your Start menu; if it is already running, click Comparison Projects.
2. On the Comparison Projects dialog box, click Export a Data Source.

   The Export a Data Source dialog box is displayed.

   Select the Other Data Source tab, if it is not already displayed. If a comparison project was selected in the Comparison Projects dialog box, then the other tabs allow you to export the data sources in that project.

3. Ensure that the Data source type is set to Live database.

   In this example, we will export a live database to a scripts folder; you can also export a SQL Compare snapshot to a scripts folder, or a scripts folder to a SQL Compare snapshot.

4. Type or select SprocketStaging in Database.

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

5. Under Output format, select Scripts folder.
6. Click Export.

   The Select a Folder dialog box is displayed.

7. Browse to the location on your computer where you want to save the creation scripts. If you are using a source control system, this may be the folder designated as your working folder. To create a new folder, type the name in the Folder name box. In this example, call the folder SprocketStaging.
8. Click Save.

The schema is exported to the specified folder as a set of script files. Browse to the location on your computer where you saved the script files.

The script files are saved in a folder according to their object type; for example, all tables are saved in the Tables subfolder, and all views in the Views subfolder. You can change the subfolder used for each object type, if required. To do this, see Setting SQL Compare Options.

Note that at this point in a development process, you may want to store the scripts in a source control system. This would allow you to roll back to this version of the schema at a later date, or to view changes to an individual object.

Comparing a database with the scripts folder

Development on the copy of the production database proceeds, and at some point a milestone is reached; the next version is ready to be tested. To compare the modified database with the schema saved as a scripts folder, set up a comparison project:

1. On the Comparison Projects dialog box, click New.

   The Project Configuration dialog box is displayed.

2. Set the Data source type on the left to Live database.
3. Type or select SprocketDevelopment in Database on the left side.

   SprocketDevelopment is the new version of the database, following a development cycle.

4. Set the Data source type on the right to Scripts folder and in the Scripts folder box, browse to the SprocketStaging folder you created. This will be the target data source in the comparison project.

   SprocketStaging is the folder containing the stored version of the original database.

   The collation and SQL Server version of the saved schema are displayed under Database Settings. For this example, leave the settings as they are.

5. Click Compare Now.

   A message dialog box is displayed. If you selected the Close dialog box on completion check box last time you ran a comparison, SQL Compare closes this message dialog box automatically.

Viewing the comparison results

The comparison results are displayed in the main window.

In the Direction bar, you can see that synchronization will change the SQL object creation scripts. The icon for the target data source indicates that it is a scripts folder.

By default, the objects are grouped by type of difference. To group by object type, select Type of Object in the Group by box.

Right-click on a group and click Expand all to view all the objects in all the groups.

To search for objects, type the search text in the Find box. In this example, search for all objects that contain 'price' by typing price in the Find box. SQL Compare searches object names and owner names.

Some objects are now identified as hidden, because they do not match the search criteria. To clear the Find box, click the button; all the objects are displayed again.

You can view a side-by-side, color-coded listing of the differences in the creation SQL, by clicking an object. For example, if you click the Sprockets table, you can see the differences for this table.

You can quickly go to lines that contain a difference using the and buttons. Click to go to the next line in the Sprockets table that is different; the two versions of the line are shown one on top of the other in the Line Viewer pane. This is especially useful when the lines are too long to view all the text; you can see more of each line in the Line Viewer. If the Line Viewer is not displayed, click to display it.

For full details of how to use the comparison results window, see Viewing the Comparison Results.

Selecting the objects to synchronize

To save the modified schema, the object creation scripts need to be updated. Synchronizing the schemas will update the scripts, which can then be saved and labelled as the new version of the Super Sprocket Company database.

To synchronize the scripts, you first select the objects you want to synchronize using the appropriate check boxes in the Include column.

For this example, all objects will be synchronized.

Synchronizing the script files

When you have selected the objects to synchronize, click Synchronization Wizard.

Synchronization changes will be made to the SQL creation scripts in the SprocketStaging folder. Click Next to view any dependencies.

In this worked example, there are no dependencies. Click Next to review the synchronization actions.

The Summary page displays the following tabs:

• Files lists the SQL creation scripts that will be modified or created when you synchronize. Note that files for objects that will be dropped during synchronization are shown as Modify. For more details, see Working with Scripts Folders.
• Action Plan provides a synopsis of the script, grouped by command type, in the order in which the commands will run
• Modifications provides a synopsis of the script, grouped by object
• Warnings displays any warnings about inefficiencies in the script, or reasons the script may fail

Click the Files tab to see the list of scripts that will be modified or created.

At this point in a development process, you may need to ensure that you have rights to access and edit the files that will be updated; when you synchronize, SQL Compare will warn you if read-only files need to be modified.

When you have looked at details of the changes to be made and viewed any warnings, click Next to go to the Confirm page.

Note that when you synchronize to a scripts folder, the SQL creation scripts in the folder are updated, but no synchronization script is produced. If you want SQL Compare to create a synchronization script, you can export the scripts folder to a snapshot and use the snapshot as the target data source. For more information, see Working with Scripts Folders.

In this example, the schemas will automatically be compared again once the synchronization is completed. If you do not want to re-compare the database schemas, clear the Compare databases following synchronization check box.

Click Finish.

A confirmation dialog box is displayed. Click Synchronize Now to continue.

Click OK to close the dialog box.

SQL Compare then re-compares the databases, and a message dialog box shows the progress of the comparison. Click OK.

The databases are compared and the results are shown in the main window. In this example, all objects are shown to be identical, confirming that the synchronization has been a success.

Close the current comparison project and browse to the SprocketStaging folder you created earlier in the example.

There are now additional subfolders containing the new object types that have been created as a result of the synchronization. In this example, the default folder structure provided by SQL Compare is used to store the SQL creation scripts for each object type.

At this point in a development process, you could check these files back into your source control system. If you do this, you would now have both the previous saved version of the schema, and a set of updated files representing the new, modified version.

Using the script files to update a live database

You now have a stored set of script files containing the next version of the Super Sprocket Company database schema. You want to compare this new development version of the database with the current production database and synchronize the schemas.

Create a new comparison project as follows:

1. Set the Data source type on the left to Scripts folder and select SprocketStaging in the Scripts Folder box.

   The SprocketStaging folder contains the new version of the schema. At this point in a development process, you may need to check out all the relevant files from your source control system.

2. Set the Data source type on the right to Live database and select SprocketProduction in the Database box.

   SprocketProduction is the production database schema you want to update with the changes made during the development process.

3. Click Compare Now.

The comparison results are displayed in the main window.

Note that the comparison results are still grouped by Type of object. SQL Compare remembers the last setting you used.

To view the synchronization script for an individual object, click on a group heading to expand the group, then select the object in the comparison results; in the SQL Differences pane, click to view the script. To switch back to the creation script, click the button again.

Now that you have reviewed the changes, you want to update the production database schema. To do this:

1. When you have selected the objects to synchronize, click Synchronization Wizard. In this example, we will synchronize all the objects (the default setting).

   Synchronization changes will be made to the SprocketProduction database.

2. Click Next to view the Dependencies page. There are no dependencies.
3. Click Next again to view the Summary page.

   In this example, SQL Compare displays a warning to inform you that the SprocketReferences table will be rebuilt. Warnings are displayed whenever tables require rebuilding as these may be slow operations.

4. Click Next to view the Confirm page.

   To save a copy of the synchronization script, ensure that Save a copy of the SQL script is selected. Saving the script means you can run it to synchronize other copies of the production database, to update them all.

5. Click Finish.
6. Click OK to close the message box.

   SQL Compare then re-compares the databases, and a message dialog box shows the progress of the comparison.

7. Click OK.