Many enterprise solutions have an existing database in place. To start working with SQL Server project developers have to migrate existing database in it. In this article, I will describe how to migrate the existing database to the database project.
Creating schema compare files
Using Schema comparison tool in Visual Studio, it is possible to compare existing database schema and the schema presented in the project. To start comparison process right-click on project → Schema Compare.
After that new windows will open. On that screen, we need to select the source and the target databases. Click on 'Select source' drop-down list, then select 'Select source' option.
On opened dialog select 'Database' radio button and press 'Select Connection' button. After that, you have to provide database connection information.
For 'Target database' select project option.
Click on 'Compare' button. It might take some time to generate differences, but after that, you will see all items which should be updated in the target database.
We will save this Schema compare in our solution. It is better to do that because you will rather frequently run the comparison. It will save some time for you in future. You can have multiple 'schema compare' items, e.g., for local database, staging or production databases. I create the _schemaCompare folder in solution to store schema compare items there. Click save schema and save it to created folder and after that add a new item to solution folder.
Configuring schema compare
Sometimes you need to exclude some objects from schema compare or set it in some specific way. It is possible to do. Click on 'Gear' button on the top panel.
After you configure the schema compare save it and check into project repository. Your schema compare will be available for all team members.
Creating database project from existing database
Now we are ready to create new database project from existing database. Double-click on local.compare.scmp file → Click Compare button → Click Update button → Done!
Conclusion
In this article, I showed how to work with schema compare in SQL Server database project and how to create database project from existing database using schema compare. Source code example for this tutorial could be found using the following link: https://github.com/aliakseimaniuk/blog-examples/tree/master/WideWorldImporters/05-Create-Database-Project-from-Database.
