Create A Database Project From Existing SQL Database

Introduction

Nowadays, many projects are built with continuous integration and continuous delivery pipelines to build and deploy the application to cloud or on-premise servers. 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 a situation, you can use a 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 an 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 the 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 a database project.

Add database to Visual Studio SQL server object explorer

Open Visual Studio and choose SQL Server Object Explorer from the 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 chose the 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 the list, click the “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 for the project and click the “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 the “Finish” button to complete the process.

The database project is created in the specified folder.

You can open the project and see the structure.

The 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 the 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 the “Tables” folder and choose the Add -> New Item option.

You can see the different types of objects in the list. Choose a Table type from the list and give a name to the table. Click the “Add” button to proceed. You can define the required column names and types for the table and click the Save button in the toolbar to save the table.

You can create as much of objects as you want. For demo purposes, I have created only one additional table.

We can create a post-deployment script to seed the data to a new table while deploying the database to the server.

Right-click the project root and choose the “Add -> New Item” option again. Select “User Scripts” and choose the “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, if you publish the database to the 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 the “Publish” button. You can click the “Edit” button to choose the server. Please give a valid name to the database.

Click the “OK” button to proceed. You can click the “Publish” button to deploy the 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 the 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 them 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 a database project. If you are working with a big team for a single application, it is easy to work collaboratively using a database project.

Conclusion

In this post, we have created a database project from an 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 for 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.