Schema Compare utility in SQL Server Data tools to Compare Different Database Definitions

SQL Server Data Tools (SSDT) includes a Schema Compare utility that you can use to compare two database definitions. The source and target of the comparison can be any combination of connected database, SQL Server database project or snapshot or .dacpac file. The results of the comparison appear as a set of actions that must be taken with the target to make it the same as the source. Once the comparison is complete you can update the target directly (if the target is a project or a database) or generate an update script that has the same effect.

The differences between source and target appear in a grid for easy review. You can drill into and review each difference in the results grid or in script form. You can then selectively exclude specific differences.

You can save comparisons either as part of a SQL Server Database project or as a standalone file. You can also set options that control the scope of the comparison and aspects of the update. Then you can save the comparison so that you can easily repeat the same comparison later or use it as the starting point for new comparison.

The following procedure compares the schema of a database project with a connected database.

Step 1 – Open Visual Studio 2010 by moving to Start > Program > Microsoft Visual Studio 2010 > Microsoft Visual Studio 2010.

Step 2 – Once Visual Studio is opened, go to Data -> Schema Compare -> New Schema Comparison

Step 3 – You will see a dialog box which requests for the source and destination db and the properties to connect to those db servers as below:

Step 4 – Choose the source and the destination DB servers which you want to compare with or you can select the database file .dbschema to compare with the source and the destination. Later on, you can save these settings as .scmpfile to use it later.

Step 5 – Once you are done with the selection of source and the destination comparison object, click on the OK button, it will take some time to get the comparison report based on the size of the data base and the objects to get compared.

Step 6 – Once the comparison is completed, the below screen will pop up which gets the result in a new list view where we can see the difference between the objects whether the object is same in both the environments, or change in the environments, etc. We can customize the view based on the requirement like, to see only the changed objects or same objects in both the schemas.

Step 7 – You can see the detailed result of the object changed in the below window of the list view as shown in the below image:

Step 8 – You can customize the schema comparison setting by going to the settings Data à Schema Compare a Schema Compare options.

Step 9 – The options available for the schema comparison can be seen in the below image:

Step 10 – We can update the schema at the later stage by exporting to the file and save it by selecting Export to Editor Window as below:

To update the schema of the target, you have two choices. You can update the target directly from the Schema Compare window if the target is a database or project, or you can generate an update script if the target is a database or a database file. A generated script appears in the Transact-SQL Editor, from which you can inspect the script execute it against a database. The following procedures describe these options further.

To update directly in the Schema Compare window

1. Click the Update button on the toolbar for the Schema Compare window.
2. Examine the change script generated. You can save the script by using the File/New menu. This can be handy for situations when you are not authorized to update a production database, in which case you can give the script to a DBA for deployment later.
3. If you have the necessary permission to update the database, click the Execute Query button in editing pane toolbar to run the script.

To update by script

1. Click the Generate Script button (the fourth one from the left) on the toolbar for the Schema Compare window.
   The generated script appears in a new Transact-SQL Editor window
2. Examine the generated change script. You can save the script by using the File/Save or File/Save As menu command.
   A saved script can be handy in situations when you are not authorized to update a production database. In these cases you can give the script to a DBA for deployment later.
   Alternatively, you can connect the Transact-SQL Editor to an appropriate server and execute the script directly. Before you can perform this procedure you must have the necessary permission to create or update the database. If you have the necessary permission to update the database, click the Execute Query button in editing pane toolbar to run the script.
3. Click the Connect button. This action either connects to the current server or prompts you to enter or select a in on the Connect to Server dialog. Notice that the database name is defined in the script as a command variable.
4. Inspect the script and, if necessary, make any changes to the command variables that define the target database name and associated prefix and the file paths.
5. Click the Execute button in the editing pane toolbar to run the script.
   
   

Source: http://msdn.microsoft.com/en-us/library/hh272690(v=vs.103).aspx