Compare the Schemas of two Databases

In this simple project without even writing a single line of code, now I'll show you how easy it is, compare the schemas of two databases and make them identical with a few steps.

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 typeNorthwind 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 mustbe placed on the sameserver 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 inMy 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!