1,632

Compare the Schemas of two Databases

Share on TwitterShare on TumblrSubmit to StumbleUponSave on DeliciousDigg This

Step 1: How to create an empty database:

Choose New from the File menu and then click Project.

This opens the New Project dialog box.

If you want to create a SQL Server 2000, click SQL Server 2000 Wizard. If you want to create a SQL Server 2005, click SQL Server 2005 Wizard.

In Name, type EmptyNW and click OK.

This opens the New Project Wizard database.

Click Configure generation / distribution.

In connection destination, click Browse and specify the connection to the database server where you want to create a blank database.

Click Finish.

A database project called SchemaDB is created and appears in Solution Explorer.

Click SchemaDB database project in Solution Explorer.

Please choose from the menu DistribuisciNomeProgetto Compilation.

The database project will be generated and distributed to the specified server.

Step 2: How to compare the schemas of two databases:

Open Database Edition Schema Compare and choose the Data menu and then click New Schema Compare.

This opens the dialog box Confrontoschema. Use this dialog to specify the source and destination. In this dialog you define the server that hosts
the source and destination, the name of each database and the type of authentication to use to connect to each database.

It will also open in the background window Database Edition Schema Compare which automatically assigns a name, such as SchemaCompare1.

In Source database in the Server name, type the name of the server that hosts the Northwind database or any other database that you want.

Under Select or enter a database name or type Northwind DB name you chose above.

In the target database in the Server name, type the name of the server where the database resides EmptyNW. This name must be placed on the
same server as the source.

Under Select or enter a database name and type EmptyNW Click Finish.

Now starts the comparison of the patterns.

After the comparison, the structural differences between the two databases will be shown in the table in the Schema Compare. The table displays
a row for each database hosted on a database. The database objects as you well know, are organized by type: tables, views, stored procedures,
roles and so on.

STEP 3: Write in the target database updates:

Schema Compare toolbar, click Write Updates.

You will perform the update actions listed in the Schema Compare window. With this timing, the pattern of the destination is changed to match
that of the source.

NB:

You can undo an action “Updating the Data menu, click Compare schema, and then clicking Stop Writing to the destination.

The comparison is not updated automatically. If you want to rerun the comparison to verify that the selected updates have been applied, click the
Refresh button on the Schema Compare toolbar.

STEP 4: Examine and run the synchronization script:

Choose Schema Compare Data menu, choose Export and then Editor. You can also click Export Editor Schema Compare toolbar.

Editor will open T-SQL in connected mode, synchronization with the T-SQL script appears. The name of this window is similar to
Server.Northwind – SchemaUpdate_EmptyNW_1.sql. This window displays the T-SQL script, a file in
My Documents / Visual Studio Projects 2005/My O Documents / Visual Studio Projects 2008/My (depends where you created the project).

As you have read and write access to this window, you can edit the script. If you change it, choose Save from the File menu.

To synchronize schemas of two databases, run the script by clicking Run SQL Schema Compare toolbar or pressing F5.

NB:

The comparison is not updated automatically. If you want to rerun the comparison to verify that the selected updates have been applied, click the
Refresh button on the Schema Compare toolbar.

At this point, you can compare data in two databases and see if they are completely identical!

Have fun to all!

prajapat