Merging Multiple Databases into a Database Project
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. |
Related Scenarios
Starting Team Development of Databases
Describes how you can create an offline representation of a database schema in a database project and add the project to version control.Starting Team Development of Databases that Reference Other Databases
Describes how you can create an offline representation of a database schema, define one or more references to other databases, define variables for target deployment environments, and add the project to version control.Starting Team Development of Databases that Reference SQLCLR Objects
Describes how you can create an offline representation of a database schema, define references to assemblies that contain SQL common language run-time (CLR) objects, define database objects that reference those objects, and add the project to version control.Starting Team Development of Large Databases
Describes how you can create an offline representation of a database schema but spread the representation across multiple projects and restrict access to parts of the schema. You can also use this approach to improve performance if you work with large databases.Starting Team Development of Databases that Reference Shared Server Objects
Describes how you can create an offline representation of a database schema, define references to a shared server project, add references to objects that are defined in the server project, and add the database project to version control.Starting Team Development of Databases that Use XML Schema Collections
Describes how you can create an offline representation of a database schema, reference an .xsd file, and use that referenced schema for typed XML columns in your tables.