How to: Import a Database Schema

  • Article

A database project is the offline representation of your database and the mechanism by which you manage changes to your database. If you created a database project but did not create or import any database object definitions, you can import a database schema from a database. If the database project already contains one or more database objects, you must use a different procedure to import additional objects or change existing objects. For more information, see How to: Import Updates from a Database into the Database Project.

Note

When you import a database schema, you also import extended properties in the source database. Extended properties on an object are defined in the same file that contains the object definition. For example, extended properties on a table MyTable will result in sp_addextendedproperty statements being added to the MyTable.table.sql file. These statements include sp_addextendedproperty, sp_settriggerorder, sp_tableoption, and sp_indexoption.

To import a database schema

  1. Either create or open an existing database project that does not already contain database objects. For more information, see How to: Create Empty Database Projects or How to: Open a Database Project.

    Solution Explorer displays the solution that contains the database project.

    Note

    You can create a project and import a schema at the same time by using the New Database Project Wizard. For more information, see How to: Create Database Projects.

  2. If Schema View does not appear, open the View menu, and click Schema View.

    Schema View appears.

  3. In Schema View, right-click the database project, and click Import Database Schema.

    The Import Database Wizard appears.

  4. In Source database connection, click the connection through which to import schema information from your existing database. If no connection appears, click New Connection to create a connection.

    You can create database connections in Server Explorer or in various other places in Visual Studio Team Edition for Database Professionals. For more information, see How to: Create a Database Connection.

  5. (Optional) Do one or more of the following:

    1. You can limit when column collations are explicitly specified to only those cases in which the column collation does not match the database collation. To create this limitation, in Import options, select the Script the column collation only if it is different from the database collation check box. Otherwise, clear that check box.

    2. If you want to ignore extended properties on the source database and its contents, in Import options, select the Ignore Extended Properties check box. Otherwise, clear that check box.

    3. If you want to update the database project settings to match the source database project settings, in Import options, select the Override database configuration with imported schema settings check box. Otherwise, clear that check box.

      Note

      Only the following settings are imported from the database: ANSI padding, ANSI nulls, ANSI warnings, Arithmetic abort, Numeric round abort, Concat null yields null, Quoted identifier, and Database collation.

  6. Click Finish to begin importing the schema.

    The Error List window displays any errors that relate to the objects that are created when the schema is imported. The Visual Studio status bar indicates when the schema has been imported. (For example, the bar should show "Import Database Schema from source database: ServerName.DatabaseName started… Import Database schema complete.") Other errors appear on the wizard summary page and are also written to a log file in the project folder.

    At this point, you must configure your database project settings before you can build and deploy your database project. For more information, see How to: Configure Database Projects for Build and Deployment.

Security

You must have permissions to access the database from which you import the database schema. In many development environments, the person in the role of the database administrator creates the database project and imports the database schema before handing the project off to the team for development work.

Note

By design, the Trustworthy database setting is not imported when you import a database schema. A setting of this nature requires sysadmin permissions and should be set explicitly. You can enable the Trustworthy setting in your database project if you have sysadmin permissions in the design-time validation database.

See Also

Tasks

How to: Import Database Objects from Script
How to: Create Database Projects
Walkthrough: Put an Existing Database under Version Control

Concepts

Terminology Overview of Team Edition for Database Professionals

Other Resources

Getting Started with Database Projects
Working with Database Projects in a Team Environment