Walkthrough: Put an Existing Database Schema Under Version Control

In this walkthrough, you learn how to put an existing database under source control, which consists of the following steps:

• You create a database project.

• You connect to an existing database.

• You import the database schema from the existing database into the database project.

• You review the results that are shown in the database project.

• You put the database project and its contents under version control.

Prerequisites

This walkthrough assumes that you have installed a copy of the sample AdventureWorks2008 database on a database server that is running SQL Server 2008. You must also have read/write permission to a version control system such as Visual Studio Team Foundation Server.

To create a database project

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

   The New Project dialog box appears.

2. Under Installed Templates, expand the Database node, and then click SQL Server.

   Note

   If you are using Visual Studio Professional, instead look under Installed Templates, expand the Database node, expand the SQL Server node, and click Advanced.

3. In the list of templates, click SQL Server 2008 Database Project.

4. In Name, type MyAdvWorks, and accept the default values for Location and Solution Name.

5. Select the Create directory for solution check box if it is not selected by default, and click OK.

   A solution is created that contains the MyAdvWorks empty database project.

   Next, you will start the Import Database Schema process, in which you specify a connection string to the source database.

To import the database schema from the existing AdventureWorks2008 database

1. In Solution Explorer, click the MyAdvWorks project node.

2. On the Project menu, click Import Database Objects and Settings.

   Note

   You can also right-click MyAdvWorks and click Import Database Objects and Settings.

   The Import Database Schema dialog box appears.

3. In the Source Database list, click the connection that corresponds to your existing AdventureWorks2008 database. If you have not yet connected to that database, you must first create a connection to it. For more information, see How to: Create a Database Connection.

4. Click Start.

   As the schema is imported, project items that correspond to the objects that are in the database appear under the database project in Solution Explorer. Schema View shows the objects that are defined in the database project. You can review the import results on the Summary page before you continue.

5. Click Finish.

   Next, you will explore the contents of the database project before you put it under version control.

To examine the results of importing the database schema

1. On the View menu, click Database Schema View.

   Schema View appears if it was not already visible.

2. In Schema View, expand the node for the database project, expand the Schemas folder, expand the HumanResources folder, and then expand the Tables folder.

   The tables in the database project should match the tables in the database.

3. Right-click the Employee table in the Tables subfolder, and click Open.

   The Transact-SQL editor appears and shows the definition for the Employee table.

4. Expand the Employee object, and expand the Constraints folder within that object.

5. Right-click the CK_Employee_BirthDate constraint in Schema View, and click Open.

   As an alternative, you can also double-click the CK_Employee_BirthDate constraint to open it.

   Another instance of the Transact-SQL editor appears and shows the definition for the CK_Employee_BirthDate constraint on the Employee table.

   In the final step, you put your solution, which contains the database project, under version control.

To put your solution under version control

1. On the File menu, point to Source Control, and click Add Solution to Source Control.

2. Click the version control project (Team Foundation Server refers to these as team projects) to which you want to add the solution by using procedures for your version control add-in.

   For more information, see Using Version Control.

   Your database project is now under version control. If you do not need to make additional changes, you should check in the files in your solution.

See Also

Concepts

Starting Team Database Development

Writing and Changing Database Code

Other Resources

Editing Database Scripts and Objects with the Transact-SQL Editor