Create A Database Project From Existing SQL Database

Introduction


Nowadays, many projects are built with continuous integration and continuous delivery pipeline to build and deploy the application to cloud or on-premise server. It is easy to perform CI/CD with database projects. If you are working with a big team, it is difficult to track changes in same database with multiple modifications from different objects. Every time you must manually update your local database, when other people make any changes in their database objects in the same application. In such situation, you can use database project with any of the common repository tools like TFS or GitHub to keep track of all changes in the database. We will see how to create a database project from existing database and add a new table to that project. We will add some seed data to the new table, so that it will be added to new database automatically while deploying the database project.

Prerequisites


  • Visual Studio 2017 or higher
  • SQL Server 2014 or higher
We are using a sample Northwind DB to create database project.

Add database to Visual Studio SQL Server Object Explorer


Open Visual Studio and choose SQL Server Object Explorer from View menu
Add a new server to the SQL server tab
 
 
 
Browse a server from the list and give credentials. Also choose a database for the project.
 
 
 
 
I have selected my local server “MURUGAN” and choose database “Northwind”. I have given windows authentication to connect the server. If you are choosing SQL server authentication, give your username and password correctly. After selecting the required database from list, click “Connect” button to proceed. You can see the added server in the SQL Server Object Explorer now.
 
 
 
Right click the database and choose “Create New Project” to proceed.
 
 
 
Choose a valid name and location to the project and click “Start” button to proceed.
 
 
 
You can choose the import permissions option to get all database related permissions, if needed.
 
This will take some time to create all database objects. Click “Finish” button to complete the process.  
 
 
 
Database project is created in the specified folder.
 
You can open the project and see the structure.
 
Project has all database objects under related folders. Here, we have tables, stored procedures and views only in the project. All the objects have individual sql script files. If you have multiple schemas, those objects will be created under corresponding folder name. Here, we have all objects under “dbo” schema. Hence, these objects are created under dbo folder.
 
 
 

Create a new Table inside the project


Right click “Tables” folder and choose Add -> New Item option.
 
 
You can see the different types of objects in the list. Choose Table type from the list and give a name to the table. Click “Add” button to proceed. You can define the required column names and types for the table and click Save button in the toolbar to save the table.
 
 
 
You can create as much of objects you want. For demo purpose, I have created only one additional table.
 
We can create a post deployment script to seed the data to new table while deploying the database to server.
 
Right click the project root and choose “Add -> New Item” option again. Select “User Scripts” and choose “Post-Deployment Script” option from the object list.
 
 
 
You can give a valid name to the script file.
 
 
 
I have just added an insert query to add a new record to the Company table. I have also checked the existence of previous records in this table. So that, if you publish the database to same server again, it will not duplicate the same record.
 
You can add any number of seed data details inside the script.

Publish database project to SQL server


We can publish the modified database project to an SQL server.
 
Right click the project and click “Publish” button. You can click the “Edit” button to choose the server. Please give a valid name to the database.
 
 
 
Click “OK” button to proceed. You can click the “Publish” button to deploy database to the server.  
 
 
You can see the publish status in the window as shown below.
 
 
 
If you have any error occurred while deployment, you can click the “View Results” link to get that exact error details.
 
We can see the newly created table and record in the object explorer. Please open the SQL server instance from SSMS or Visual Studio.
 
 
We can modify the objects in the database project as per our requirement and again deploy to the same database. This will not affect the existing data in the database. You can add or delete a column from a table or modify the stored procedure easily with database project. If you are working with a big team for a single application, it is easy to work collaboratively using database project.

Conclusion


In this post, we have created a database project from existing SQL database and added a new table to the project. We have added seed data to a table inside the post deployment script. We have successfully published the database project to a local SQL server. As I mentioned earlier, database projects are very useful to work with a big team and it is easy to perform with continuous integration and continuous delivery pipelines. This will reduce the headache from SQL developers to maintain updated scripts throughout the application. Multiple people can work in the same database object and track the changes very easily. Please give your valuable comments on this post.