Create SQL Server Database Project With Visual Studio

In this article, we’re going to learn about how to we can manage our database scripts using Visual Studio’s SQL server database project.

Introduction

When we work on any project, a database plays an important role and after time when the number of tables, views and procedures increases - it becomes too difficult to manage the scripts.

And even after that when we manage the scripts, then we still need to compare which script we had worked on and which are remaining on any particular database. There are many good tools available in the market for comparing databases but most of them are paid.

So, in this article we’re going to learn about how to we can manage our database scripts using Visual Studio’s SQL Server Database Project.

We can create a new database project and import database schema from an existing database, a .sql script file or a Data-tier application (.dacpac). We can then invoke the same visual designer tools (Transact-SQL Editor, Table Designer) available for connected database development to make changes to the offline database project, and publish the changes back to the production database. The changes can also be saved as a script to be published later. Using the Project Properties panel, we can change the target platform to different versions of SQL Server (including SQL Azure).

Prerequisite

VS2015 or VS2017 should be installed in your machine.

SQL server – to import database script and to publish new or altered scripts.

Let’s start!!

  1. Open VS and create a new project from the Menu, Select File >> New >> Project.

    SQL Server Database Project
  1. Select SQL Server >> SQL Server Database Project. Now, Enter the Name of the project and then press OK.

    SQL Server Database Project
  1. After the project is created, we can see database project in Solution Explorer.

    SQL Server Database Project
  1. Right Click on DemoDatabase Project and then Import >> .dacpac or Database/Script to import scripts from the existing database.

    SQL Server Database Project
  1. We then select Database >> Select connection.

    SQL Server Database Project
  1. Enter credentials and database name >> connect

    SQL Server Database Project
  1. Now, we can see server name, database name in Source database connection >> Start

    SQL Server Database Project
  1. Successfully imported all the scripts for logins, schemas, tables, procedures etc >> Select Finish 

    SQL Server Database Project
  1. All the scripts are imported to the solution – now we can add or alter scripts from here.

    SQL Server Database Project
  1. Let’s create a replica of the azure database which we imported to the solution on the local server – Right click on properties and select Target

    SQL Server Database Project
  1. To publish scripts to the database, select Publish

    SQL Server Database Project
  1. Now, select the server and database where we have to publish scripts - Edit button

    SQL Server Database Project
  1. Enter server name, authentication and then select database from the drop-down. Press Test Connection just to ensure the given information is correct. Then press OK.

    SQL Server Database Project
  1. We can save a profile using Save Profile As button - so that we don’t have to re-enter connection related settings next time. After that, we can Generate Script or directly publish changes to the database.

    SQL Server Database Project
  1. If we Press Generate Script, we can see compared script in visual studio’s new tab. We can check the script and after that press Execute

    SQL Server Database Project
  1. Changes are published successfully to the local database.

    SQL Server Database Project
  1. We can check our Database using SQL Server Management Studio.

    SQL Server Database Project

All the tables, procedures, views, etc were populated as is. We don't need to write any migration script like we used to do for DB migration.

And after this, whenever there are changes in the tables or the procedures just make the changes to the scripts in solution and publish it to the database – VS will create the alter script accordingly.

So, managing our database now becomes easier using SQL Server Database Project.