Walkthrough: Comparing the Schemas of Two Databases

Article
02/04/2013

This topic applies to:

Visual Studio Ultimate	Visual Studio Premium	Visual Studio Professional	Visual Studio Express

In this walkthrough, you compare the schemas of two databases by using Visual Studio. The schema-comparison action also generates a Data Definition Language (DDL) script from the differences. You use this file to synchronize the schema of the target with that of the source. For more information, see Compare and Synchronize Database Schemas.

You will follow these procedures in this walkthrough:

Create a simple database. If you have completed Walkthrough: Comparing the Schemas of a Database and Database Project, you can use the resulting database and can skip this part of the walkthrough.
Create an empty database. The CompareProjectDB database serves as the source, and you create an empty database to serve as the target.
Compare the schemas of two databases. By comparing schemas, you find the structural differences between the databases, display the differences in a table, and generate a DDL script that expresses the differences.
Examine the synchronization script. You check the DDL script, and you can edit it before you run it.
Update the target database. You run the DDL script to change the schema of the target.

Prerequisites

You must have the following products installed:

SQL Server 2008
Permissions to create and update databases on the target database server
Visual Studio Premium or Visual Studio Ultimate

Create a Simple Database

You will create a simple database by performing the following tasks:

Create a script that contains a database schema
Create a database project and import that schema
Deploy the database project to an isolated development environment

Create a Script that Contains a Database Schema

To create a script from which you can import a schema

On the File menu, point to New, and then click File.

The New File dialog box appears.
In the Categories list, click General if it is not already highlighted.
In the Templates list, click Sql File, and then click Open.

The Transact-SQL editor opens.

Copy the following Transact-SQL code, and paste it into the Transact-SQL editor.

PRINT N'Creating Sales...';
GO
CREATE SCHEMA [Sales]
    AUTHORIZATION [dbo];
GO
PRINT N'Creating Sales.Customer...';
GO
CREATE TABLE [Sales].[Customer] (
    [CustomerID]   INT IDENTITY (1, 1) NOT NULL,
    [CustomerName] NVARCHAR (40) NOT NULL,
    [YTDOrders] INT NOT NULL,
    [YTDSales] INT NOT NULL
);
GO
PRINT N'Creating Sales.Orders...';
GO
CREATE TABLE [Sales].[Orders] (
    [CustomerID] INT NOT NULL,
    [OrderID] INT IDENTITY (1, 1) NOT NULL,
    [OrderDate] DATETIME NOT NULL,
    [FilledDate] DATETIME NULL,
    [Status] CHAR (1) NOT NULL,
    [Amount] INT NOT NULL
);
GO
PRINT N'Creating Sales.Def_Customer_YTDOrders...';
GO
ALTER TABLE [Sales].[Customer]
    ADD CONSTRAINT [Def_Customer_YTDOrders] DEFAULT 0 FOR [YTDOrders];
GO
PRINT N'Creating Sales.Def_Customer_YTDSales...';
GO
ALTER TABLE [Sales].[Customer]
    ADD CONSTRAINT [Def_Customer_YTDSales] DEFAULT 0 FOR [YTDSales];
GO
PRINT N'Creating Sales.Def_Orders_OrderDate...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [Def_Orders_OrderDate] DEFAULT GetDate() FOR [OrderDate];
GO
PRINT N'Creating Sales.Def_Orders_Status...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [Def_Orders_Status] DEFAULT 'O' FOR [Status];
GO
PRINT N'Creating Sales.PK_Customer_CustID...';
GO
ALTER TABLE [Sales].[Customer]
    ADD CONSTRAINT [PK_Customer_CustID] PRIMARY KEY CLUSTERED ([CustomerID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO
PRINT N'Creating Sales.PK_Orders_OrderID...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [PK_Orders_OrderID] PRIMARY KEY CLUSTERED ([OrderID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO
PRINT N'Creating Sales.FK_Orders_Customer_CustID...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [FK_Orders_Customer_CustID] FOREIGN KEY ([CustomerID]) REFERENCES [Sales].[Customer] ([CustomerID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
PRINT N'Creating Sales.CK_Orders_FilledDate...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [CK_Orders_FilledDate] CHECK ((FilledDate >= OrderDate) AND (FilledDate < '01/01/2010'));
GO
PRINT N'Creating Sales.CK_Orders_OrderDate...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [CK_Orders_OrderDate] CHECK ((OrderDate > '01/01/2005') and (OrderDate < '01/01/2020'));
GO
PRINT N'Creating Sales.uspCancelOrder...';
GO
CREATE PROCEDURE [Sales].[uspCancelOrder]
@OrderID INT
AS
BEGIN
DECLARE @Delta INT, @CustomerID INT
BEGIN TRANSACTION
    SELECT @Delta = [Amount], @CustomerID = [CustomerID]
     FROM [Sales].[Orders] WHERE [OrderID] = @OrderID;

UPDATE [Sales].[Orders]
   SET [Status] = 'X'
WHERE [OrderID] = @OrderID;

UPDATE [Sales].[Customer]
   SET
   YTDOrders = YTDOrders - @Delta
    WHERE [CustomerID] = @CustomerID
COMMIT TRANSACTION
END
GO
PRINT N'Creating Sales.uspFillOrder...';
GO
CREATE PROCEDURE [Sales].[uspFillOrder]
@OrderID INT, @FilledDate DATETIME
AS
BEGIN
DECLARE @Delta INT, @CustomerID INT
BEGIN TRANSACTION
    SELECT @Delta = [Amount], @CustomerID = [CustomerID]
     FROM [Sales].[Orders] WHERE [OrderID] = @OrderID;

UPDATE [Sales].[Orders]
   SET [Status] = 'F',
       [FilledDate] = @FilledDate
WHERE [OrderID] = @OrderID;

UPDATE [Sales].[Customer]
   SET
   YTDSales = YTDSales - @Delta
    WHERE [CustomerID] = @CustomerID
COMMIT TRANSACTION
END
GO
PRINT N'Creating Sales.uspNewCustomer...';
GO
CREATE PROCEDURE [Sales].[uspNewCustomer]
@CustomerName NVARCHAR (40)
AS
BEGIN
INSERT INTO [Sales].[Customer] (CustomerName) VALUES (@CustomerName);
SELECT SCOPE_IDENTITY()
END
GO
PRINT N'Creating Sales.uspPlaceNewOrder...';
GO
CREATE PROCEDURE [Sales].[uspPlaceNewOrder]
@CustomerID INT, @Amount INT, @OrderDate DATETIME, @Status CHAR (1)='O'
AS
BEGIN
DECLARE @RC INT
BEGIN TRANSACTION
INSERT INTO [Sales].[Orders] (CustomerID, OrderDate, FilledDate, Status, Amount) 
     VALUES (@CustomerID, @OrderDate, NULL, @Status, @Amount)
SELECT @RC = SCOPE_IDENTITY();
UPDATE [Sales].[Customer]
   SET
   YTDOrders = YTDOrders + @Amount
    WHERE [CustomerID] = @CustomerID
COMMIT TRANSACTION
RETURN @RC
END
GO

On the File menu, click Save SqlQuery_1.sql As.

The Save File As dialog box appears.
In Object name, type SampleImportScript.sql.

You can save the file to any location on your computer. Make note of the location because you must use this script in the next procedure.
Click Save.
On the File menu, click Close Solution.

Next you create a database project and import the schema from the script that you have created.

Create a Database Project and Import a Schema

To create a database project

On the File menu, point to New, and click Project.

The New Project dialog box appears.
Under Installed Templates, expand the Database node, and then click SQL Server.
In the list of templates, click SQL Server 2008 Database Project.
In Name, type CompareProjectDB.
In the Solution list, click Create Solution if it is not already highlighted.
Select the Create directory for solution check box if it is not already selected.
Clear the Add to Source Control check box if it is not already cleared, and click OK.

The database project is created and appears in Solution Explorer. Next, you import the database schema from a script.

To import a database schema from a script

On the Project menu, click Import Script.
Click Next after you read the Welcome page.
Click Browse, and indicate the path where you saved the SampleImportScript.sql file.
Double-click the SampleImportScript.sql file, and click Finish.

The script is imported, and the objects that are defined in that script are added to your database project.
Review the summary, and then click Finish to complete the operation.

Note

The Sales.uspFillOrder procedure contains an intentional coding error that you discover and correct in the unit testing walkthrough.

To examine the resulting project

In Solution Explorer, expand the Schema Objects child node.
Explore the subnodes under the Schema Objects node in the hierarchy.

Solution Explorer contains the files that define the database objects.
On the View menu, click Database Schema View.
In Schema View, expand the CompareProjectDB node.
Explore the subnodes under the CompareProjectDB node in the hierarchy.

Schema View contains the objects that are defined in the files that appear in Solution Explorer.

Deploying to an Isolated Development Environment

Next, you deploy the project to create a database that has the imported schema but no data. You create this database in an isolated development environment, or sandbox. Therefore, you can develop and test the database with no interference from other efforts.

To configure and build the database project

In Solution Explorer, click the database project CompareProjectDB.
On the Project menu, click CompareProjectDB Properties.

The properties dialog box for the project appears.
Click the Deploy tab.
In the Configure deployment settings for list, click My isolated development environment. By configuring settings for your isolated development environment, you can use different deployment settings than those that will be used in your other environments, such as the testing or production server.
In the Deploy Action list, click Create a deployment script (.sql) and deploy to database.
In Target Database Settings, click Edit.

The Connection Properties dialog box appears.
Set the connection properties for the database that you want to create, and then click OK.

In the Target Connection box, the correct connection string appears.

Warning

You should create the database on a test server, a development server, or on your local computer. You should not specify your production server.
In Target database name, type CompareProjectDB.
Next to Deployment configuration file, click Edit.
Clear the Block incremental deployment if data loss might occur check box.

Note

For this walkthrough, you will test the stored procedures against an empty database that you deploy as part of the database unit test. You do not have to preserve any existing data because you will test the stored procedures in your isolated development environment.
On the File menu, click Save All.
On the Build menu, click Build Solution.

The properties that you have just set determine how the deployment script is built. The status of the build appears in the Output window, and Build: 1 succeeded or up-to-date should appear as the last line.

To deploy the database project

In Solution Explorer, click the CompareProjectDB database project.
On the Build menu, click Deploy CompareProjectDB.

Warning

You should run this deployment against a test server, a development server, or your local computer. You should not specify your production server.

The database project is deployed to a new database. The status of the deployment appears in the Output window, and Deployment succeeded should appear as the last line. Next you create an empty database that you can compare to this deployed database.

Create an Empty Database

To create an empty database

On the File menu, point to New, and then click Project.

The New Project dialog box appears.
In the list of Installed Templates, expand the Database node, and click SQL Server.
Click SQL Server 2008 Wizard.
In Name, type EmptyDB.
Accept the defaults for the remaining fields, and then click OK.
In the New Database Project Wizard, click Configure Build/Deploy.
In the Deploy action list, click Create a deployment script (.sql) and deploy to the database.
In Target connection, click the Browse button.
Specify the connection to the database server where you want to create the empty database, and click Finish.

A database project that is named EmptyDB is created and appears in Solution Explorer.
In Solution Explorer, click the EmptyDB database project.
On the Build menu, click Deploy EmptyDB.

The database project is built and deployed to the specified server.

Compare the Schemas of Two Databases

To compare the schemas of two databases

On the Data menu, point to Schema Compare, and then click New Schema Comparison.

The New Schema Comparison dialog box appears so that you can specify the source and the target. In this dialog box, you identify the servers on which the source and the target reside, the name of each database, and the type of authentication to use when you connect to each database.

Also, the Schema Compare window opens in the background, and Visual Studio automatically assigns it a name such as SchemaCompare1.
Under Source Schema, click Database, and then click the connection that corresponds to the CompareProjectDB database.

If no such connection appears in the list, click New Connection. In the Connection Properties dialog box, identify the server on which the CompareProjectDB database resides, the type of authentication to use when you connect to it, and the database itself. When you are finished, click OK.

Note

After you establish a connection, it appears in Server Explorer under Data Connections.
Under Target Schema, click Database, and then click the connection that corresponds to the EmptyDB database.

If no such connection appears in the list, click New Connection. In the Connection Properties dialog box, identify the server on which the EmptyDB database resides, the type of authentication to use when you connect to it, and the database itself. When you are finished, click OK.
Click Options to specify which objects are compared, what types of differences are ignored, and what the generated update script will contain.

Note

When you compare databases, you do not specify SQLCMD variables.
On the General tab, expand the Comparison options node.
Select Ignore file names and paths for files and log files.
Click OK.
Click OK.

The schema comparison starts.

Note

You can stop a comparison that is in progress by opening the Data menu, pointing to Schema Compare, and clicking Stop Schema Compare.

You can configure options that determine what is considered a difference and that change how the update script is created. For more information, see How to: Set Options for Comparing Database Schemas.

When the comparison is finished, the structural differences between the two databases appear in the table in the Schema Compare window. The table displays a row for each database object that exists in either database. Database objects are organized by type: tables, views, stored procedures, roles, and so on.

Update the Target Database

To update the schema of the target, you have two choices. You can update the schema directly from the Schema Compare window or by using the Transact-SQL editor. This section describes both choices.

After you have run the schema comparison, the structural differences appear in the table in the Schema Compare window. For each object in the CompareProjectDB (Source Database) column, the table displays an action in the Update Action column that would be necessary to synchronize that object in the two databases. In this case, because the target is empty except for default objects, the Status column contains mostly New statuses and the Update Action column contains mostly Create actions.

Write Updates to Target

You can update the schema of the target using the update actions listed in the Schema Compare window. To perform this task, follow the steps in Write Updates to Target Database.

View Script, and then Write Updates to Target

You can export an update script, examine it, possibly change it, and then use it to synchronize the target database. To perform these tasks, follow the steps in Examine and Execute the Synchronization Script.

Write Updates to Target Database

Note

Before you write updates to a production or development server, you should consider backing up your target database. Because some schema changes cannot be performed in a single transaction, you might lose data if you start and then cancel an update operation. For example, a table in the target database might have been dropped in preparation for its re-creation. If you cancel the update at that moment, you could lose the table. In this walkthrough, you are updating an empty development database. Therefore, you do not back up the target database.

To write updates to the target database

In the list of comparison results, scroll to the SQL Files node.
For the CompareProjectDB file and the CompareProjectDB_Log file, change the update action from Create to Skip.

Note

If you compare two databases on the same database server, synchronization will fail because the target data and log files already exist and are being used by the source database. For this walkthrough, you just skip the updates to the files and synchronize the contents of the database.
For the EmptyDB file and EmptyDB_log file, change the Update Action from Drop to Skip.

Note

Because you are not creating the files from the source database, you must not delete the files for the target database. For this walkthrough, you just skip the updates to the files and synchronize the contents of the database.
On the Schema Compare toolbar, click Write Updates.

The update actions that were listed in the Schema Compare window are executed. This synchronization changes the schema of the target to match that of the source.

Note

While the update action is occurring, you can cancel the operation by opening the Data menu, pointing to Schema Compare, and then clicking Stop Writing to Target.

If you want to run the comparison again to verify that the selected updates were applied, you can click Refresh on the Schema Compare toolbar.

Examine and Execute the Synchronization Script

To examine the synchronization script

On the Data menu, point to Schema Compare, point to Export to, and then click Editor.

As an alternative, you can also click Export to Editor on the Schema Compare toolbar.

The Transact-SQL editor opens in disconnected mode and displays the Transact-SQL synchronization script. This window has a name such as Server.CompareProjectDB - SchemaUpdate_EmptyDB_1.sql. and displays the Transact-SQL script. Because you have both write and read access in this window, you can change the script. If you change it, open the File menu, and then click Save. When you save the file, you can specify its path and name.
On the Transact-SQL Editor toolbar, click SQLCMD Mode.

If you do not enable SQLCMD mode, errors will appear when you try to execute the script.
To synchronize the schemas of the two databases, run this script by clicking Execute SQL on the Transact-SQL Editor toolbar or by pressing F5.

The Connect to Database dialog box appears.
Click the connection that corresponds to the EmptyDB database, and click OK.

Warning

If you attempt to run the update script against a different database, you might cause results that you did not intend.

The comparison is not refreshed automatically. If you want to run the comparison again to verify that the selected updates were applied, you must click Refresh on the Schema Compare toolbar.

Next Steps

Now, you can compare the data within the two databases. For more information, see How to: Compare and Synchronize the Data of Two Databases.