How to: Import Database Objects and Settings

  • Article

If you want to manage a database that already contains definitions of database objects, you can create an empty database project and import the objects and settings from your database into the project. You can import objects and settings by using the Import Database Wizard or by using an automation command from the Command Window. If you want to add database objects to a project that is not empty, you must use a different procedure to import additional objects or to change existing objects. For more information, see How to: Import Updates from a Database into the Database Project.

Note

When you import from a database, passwords are not imported. After an import operation completes, passwords in the project are set to random characters. If you compare schemas, password differences are not reported.

Required Permissions

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

To import database objects and settings by using the Import Database Wizard

  1. Either create or open a database project that does not already contain database objects.

    For more information, see How to: Create Empty Database and Server Projects.

    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 and Server Projects.

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

    Schema View appears.

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

    The Import Database Wizard appears.

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

    You can create database connections in Server Explorer or in various other locations. For more information, see How to: Create a Database Connection.

  5. (Optional) Under Import options, 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, 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 import extended properties on the source database and its contents, select the Import Extended Properties check box. Otherwise, clear that check box.

      For example, you can import a table that is named MyTable. This action will add sp_addextendedproperty statements to the MyTable.table.sql file. These statements include sp_addextendedproperty, sp_settriggerorder, sp_tableoption, and sp_indexoption. For more information, see this topic on the Microsoft Web site: sp_addextendedproperty (Transact-SQL).

    3. If you want to import the sizes for log files and filegroups, select the Import log and filegroup file sizes check box. Otherwise, clear that check box.

    4. If you want to import permissions from the source database, select the Import permissions check box. Otherwise, clear that check box.

      If you import permissions, you can specify whether those permissions are added to the model of the database project. If you add the permissions to the model, your database project will load more slowly.

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

    6. You can decrease the amount of time that it takes to open and work with your database project by limiting the number of database objects that are stored in each folder in your project. In the Maximum files per directory list, click the number of files that you want to allow in a single directory on disk.

  6. Click Start to import the schema.

    The Error List window displays any errors that relate to the objects that are created when the schema is imported. When the schema has been imported, the status bar indicates 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 Properties for Database Projects.

To import database objects and settings by using the Visual Studio automation model

  1. Either create or open a database project that does not already contain database objects.

    For more information, see How to: Create Empty Database and Server Projects.

    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 and Server Projects.

  2. Open the View menu, point to Other Windows, and click Command Window.

  3. In the Command Window, type the following command:

    Project.ImportDatabaseSchema /ConnectionString "YourConnectionInfo"

    Replace YourConnectionInfo with the connection string that you use to connect to the database from which you want to import.

    Note

    You can specify additional options for the command that control the import operation. For more information, see Automation Command Reference for Database Features of Visual Studio. If you do not specify a connection, the Import Database Wizard appears.

    The objects and settings are imported from the specified database into the database project that was selected in Solution Explorer when you executed the command. The Error List window displays any errors that relate to the objects that are created when the schema is imported.

    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 Properties for Database Projects.

See Also

Tasks

How to: Import Database Objects from a Script

How to: Create Database and Server Projects

Walkthrough: Put an Existing Database Schema Under Version Control