Merging Multiple Databases into a Database Project

  • Article

You can merge multiple databases by creating a database project, importing database objects into it from each source database, and then deploying the project as a single database. After you create the project, you typically import all objects from the first database. Then you compare the schema of each subsequent database with the schema of the project and specify which objects you want to import. You must also specify that objects that you already imported should be skipped, instead of dropped, when you update the schema for the database project.

Importing Objects from Multiple Databases

Before you import objects from multiple databases, you should consider the following additional issues:

File and Filegroups

By default, you will import any files and filegroups that are defined for each database into your database project. If you do not want to import these files, you must specify that they should be skipped when you import the other objects. For example, you might want to import the files and filegroups from only the first database from which you import objects.

Name Conflicts

If you import a database object that has the same name as an object that you already imported, the object that you already imported will be overwritten. To retain both objects, you must rename at least one of them before you import the second object.

Full-text Catalogs

The paths that are associated with full-text catalogs might not be valid when you move the catalogs between databases. If you are merging databases that have full-text catalogs, you should verify the catalog properties before you deploy the database project.

Common Tasks

Common Tasks

Supporting Content

Learn more about database projects: You can read about the basic concepts of how to manage schema changes by using database projects.

Create a database project, and import objects and settings from the first database: You can create a project, configure project settings, and import a schema by using a wizard. You can also create an empty project if you want to import objects later or if you do not have permission to access the first database from which you want to import objects.

Merge other databases into the project: You can compare the schema of a subsequent source database to the schema of the database project. For any objects that you do not want to import or that you already imported, you must specify that they should be skipped, instead of dropped or updated, when you import the other objects. For objects that you do want to import, you must specify that they should be updated. You repeat this process for each database that you want to merge into the project.

Update data generation plans: If one or more of the databases that you want to merge had data generation plans associated with them, you can add those plans to your project. After you add each plan, you must then open it so that it synchronizes with the updated database schema. You can also create a data generation plan for the combined database.

Update database unit tests: If you had existing database unit tests for the databases that you want to merge, you must update the test configuration for those tests to use the correct connection string. You can also update the database schema and the data generation plan for the configuration.

Put the database schema under version control: After you have imported all objects and settings and updated your data generation plan, you can add your solution to version control to make it available to the members of your team.