Walkthrough: Comparing the Schemas of a Database and Database Project

  • Article

In this walkthrough, you compare the schema of a database project with the schema of a database by using Team Edition for Database Professionals.

Depending on how your team is using the database project and the database, you might then want to copy schema changes in one direction or another. The following scenarios are typical:

  • Project is Source and Database is Target. You can use a database project to develop or maintain a database. After you make schema changes in the project, you copy them to the database, which is hosted on a staging server. Later, your team could deploy the database to a production server.

    The schema-comparison action that you perform in this walkthrough generates a Data Definition Language (DDL) script from the schema differences. You can then use this script to apply all or part of the database project to the database. For more information, see Propagate changes from the project to the database.

  • Database is Source and Project is Target. An error might be found in the schema of a production database, or the schema might become outdated and require an update. This discovery might result in an urgent update being applied to the database. To keep the project synchronized with the database, you can then import the schema changes that were caused by the urgent update into a database project. For more information, see How to: Import Updates from a Database into the Database Project.

Tasks illustrated in this walkthrough include:

Setup Phase

  1. Create a database project. The new project starts out empty.

  2. Compare the database schema with the project schema. In this comparison, you designate the database project as the source and the database as the target. After the comparison is complete, this configuration lets you write schema updates from the database project to the database.

    Note

    You can also propagate changes from a source database to a target database project. For more information, see How to: Import Updates from a Database into the Database Project.

  3. Import the schema of the database into the project. The database and the project now have identical schemas.

Production Phase

  1. Add a table to the database project. You will add a table called InternationalShippers, which has three columns.

  2. Compare the two schemas again. The InternationalShippers table appears as new in the database project.

  3. Propagate changes from the project to the database. You can choose to propagate the new InternationalShippers table to the Northwind database. Following this procedure would overwrite parts of the example Northwind database.

Prerequisites

To complete this walkthrough, you will need:

  • Microsoft SQL Server 2000 or Microsoft SQL Server 2005

  • Microsoft Visual Studio 2005 with Team Edition for Database Professionals 

  • The Northwind database

Setup Phase

Create a Database Project

To create a database project

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

    The New Project dialog box opens.

  2. Under Project types, expand Database Projects, and then click Microsoft SQL Server.

  3. Under Templates, click SQL Server 2005.

  4. In Name, type Northwind_Project, and then click OK.

    The Northwind_Project opens and appears in Solution Explorer. The project is empty.

    Note

    By using Team Edition for Database Professionals, you can also create projects that are not empty. For more information, see How to: Create Database Projects.

Compare the Database Schema with the Project Schema

To compare the two schemas

  1. (Optional) Configure options that affect how schemas are compared. For more information, see Options (Database Tools/Schema Compare) and How to: Set Options for Comparing Database Schemas.

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

    The New Schema Comparison dialog box opens. Under Source Schema, the Northwind_Project project appears.

  3. Under Target Schema, make sure that Database is clicked, and then click the Northwind database.

    Note

    If the list under Database is empty, click New Connection. In the Connection Properties dialog box, connect to the database server that contains the Northwind database, and click OK. If the list under Database is not empty and you connect to the Northwind database using SQL Server Authentication, the Connect to SQL Server dialog box appears. You must specify your credentials to connect to the database.

  4. In the New Schema Comparison dialog box, click OK.

    The schema of the Northwind database is compared with the schema of your Northwind_Project project. The comparison results appear in the Schema Compare window. The update action is listed as Drop for every object that exists in the Northwind database that does not exist in the Northwind_Project project.

Import the Database Schema into the Project

To import the database schema

  1. On the View menu, click Schema View.

    Schema View opens and displays the schema of the Northwind_Project project.

  2. Right-click Northwind_Project, and click Import Database Schema.

    The Import Database Wizard opens.

  3. Under Source database connection, click the Northwind database.

  4. Click Finish.

    The schema of the database is imported into the project. The two schemas are now identical.

    Note

    The database project inherits many properties from the Northwind database. To display these properties, right-click Northwind_Project in Solution Explorer, and then click Properties. The imported settings include Default Collation on the Project Settings page and the Miscellaneous settings on the Database Properties page.

  5. To verify that the two schemas are identical, click Refresh on the Schema Compare toolbar.

    The schemas are compared again. For schema objects that are identical, the update action is shown as Skip.

Production Phase

Add a Table to the Database Project

To add a table to the project

  1. On the View menu, click Schema View.

    Schema View opens and displays the schema of the Northwind_Project project.

  2. In Schema View, expand the Northwind_Project node.

  3. Right-click Tables, point to Add, and click Table.

    The Add New Item - Northwind_Project dialog box opens.

  4. Under Templates, click Table.

  5. In Name, type InternationalShippers, and then click Add.

    A table named InternationalShippers is added to the Northwind_Project project. The table definition appears in the Transact-SQL (T-SQL) editor.

  6. Change the SQL script in InternationalShippers.table.sql to the following:

    CREATE TABLE [dbo].[InternationalShippers]
(
[ShipperID] [int] NOT NULL IDENTITY(1,1),
[CompanyName] [nvarchar] (40) NOT NULL,
[Region] [nvarchar] (40) NOT NULL,
[Phone] [nvarchar] (24) NULL
) ON [PRIMARY]

  7. Click Save InternationalShippers.table.sql.

  8. In Schema View, right-click the InternationalShippers table, and click View File in Solution Explorer.

    The InternationalShippers.sql file is highlighted in Solution Explorer.

  9. Press F4.

    The Properties window appears and displays the properties of the InternationalShippers.table.sql file. Notice that the Build Action is set to Build. By setting the Build Action property to Build, you indicate that the file contains the definition for a database object and should be parsed and validated.

Compare the Two Schemas

To compare the two schemas

  1. Click the Schema Compare tab to redisplay the results of the most recent comparison.

  2. On the Schema Compare toolbar, click Refresh.

    The schema of the Northwind database is compared with the schema of your changed Northwind_Project project. The comparison results appear in the Schema Compare window. In the Northwind_Project column, the [dbo].[InternationalShippers] table appears.

    The status of the new table is New, and its update action is Create. If you propagated changes now, the table would be created in the target database. For more information, see the following procedure.

Propagate Changes from the Project to the Database

In this procedure, you propagate the new InternationalShippers table to the Northwind database. For more information, see How to: Synchronize Database Schemas.

Warning

This procedure is described for illustrative purposes only. You probably do not want to follow the steps in this procedure because doing so would overwrite parts of the example Northwind database. Use this procedure only if you do not need to maintain a pristine copy of Northwind. As an alternative, you could perform the procedure on a copy of the Northwind database.

To propagate changes to the target database

  • In the Schema Compare window, click Write Updates.

    The update actions that were listed in the Schema Compare window, which includes the update action of Create for the InternationalShippers table, are executed. This synchronization changes the schema of the target database to match that of the source database project.

    Note

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

    Warning

    Because some schema changes cannot be transacted, you can lose data if you cancel an update operation. For example, a table in the target database may have been dropped in preparation for its re-creation. If you cancel the update at that moment, you could lose the table. If this type of data loss occurs, you will want to compare the schemas again.

See Also

Tasks

How to: Compare the Schemas of Two Databases
How to: Compare the Data of Two Databases

Concepts

Terminology Overview of Team Edition for Database Professionals

Other Resources

Comparing Database Schemas
Comparing Database Data