Compare And Update SQL Server Database Project With Visual Studio

In this article, you will learn how to create a SQL Server Database project using Visual Studio 2019 and how to compare and update the SQL Server Database Project from the remote SQL Server Database and vice versa.

Introduction

 
When we are working on any project, a database plays an important role and after some time when the number of tables, views, and store procedures increases, it becomes too difficult to manage the database changes. It's very difficult to update production database projects.
 
And even after that when we manage the database, 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 compare 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 or VS2019 should be installed in your machine.
 

Getting Started

  • Open Visual Studio and create a new project from the Menu, Select File > New > Project.
  • Select the SQL Server Database Project from the list.
Compare And Update SQL Server Database Project With Visual Studio
  • Enter the Project name and click on create button.
Compare And Update SQL Server Database Project With Visual Studio
  • After the project is created, we can see the database project in the Solution Explorer.
Compare And Update SQL Server Database Project With Visual Studio
  • Right-click on SchemaCompareDemo project and then click on Schema Compare option from the context menu. 
Compare And Update SQL Server Database Project With Visual Studio
  • Then click on Select Target to select the database. 
Compare And Update SQL Server Database Project With Visual Studio
  • Then click on the select connection.
Compare And Update SQL Server Database Project With Visual Studio
  • Select your database or enter new database credentials. Select database name from the drop-down and click on connect.
Compare And Update SQL Server Database Project With Visual Studio 
  • After the database, selection clicks on the switch button to switch source with target because we want to update our local DB project from a remote database.
Compare And Update SQL Server Database Project With Visual Studio
  •  Start schema comparison by clicking on the Compare button.
Compare And Update SQL Server Database Project With Visual Studio
  •  The comparison view will be shown after some time. You can select the change which you want to make into your local SQL Server Database Project. After selection click on the Update button.
Compare And Update SQL Server Database Project With Visual Studio
  • After the schema update is completed, open your Solution Explorer. As you can see that your SQL Server Database Project has been updated from the remote database. 
Compare And Update SQL Server Database Project With Visual Studio
 
All the tables, stored procedures, views and etc. have been updated from the remote database.
 
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 SQL Server Project Database in the solution, compare and update your changes to the target database. Visual Studio will update the target database accordingly.
 
So, managing our database now becomes easier using SQL Server Database Project and now there is no need to worry about production db updates.