Thursday, May 18, 2017

Creating SQL Server database project from existing database

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.

Schema Compare in Visual Studio 2017
 
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.

Select source schema dialog  in Visual Studio 2017

On opened dialog select 'Database' radio button and press 'Select Connection' button. After that, you have to provide database connection information.

Connect to database screen

For 'Target database' select project option.

Select target schema dialog in Visual Studio 2017

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.

Compare results view

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.

Project structure with schema compare 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. 

Schema compare configuration

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.