Create and Publish SQL Server Database Project With Visual Studio

Introduction

Databases plays an important role in any project these days. It becomes too difficult to manage the objects such as Tables, Views, Procedures and Functions increases over the time and also difficult to manage when number of team members works in Database at the same time. 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. Developers have been using source control for a long time, but there just wasn’t a good way to get database objects under source control. As a result, there are many applications out there with the ‘application code’ in source control, but the database code is unmanaged. 

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. Continuous Integration has become standard practice for many development projects, we can also do using Visual Studio SQL Server Database Project. We can use Visual Studio to run the Import Database Wizard and populate an empty Database Project.

Creating Database Project in Visual Studio

While writing this article, I am using the Visual Studio 2019 version.

• Open Visual Studio (2019)

• Click Create a new project
• Choose Query Language from the Language dropdown and choose SQL Server Database Project as project template

• Configure our new project by providing the project name by giving meaningful name and Location to save the project by clicking Create button

• Once after the configuration, this is how the SQL Server Project looks (initial look).

• Right Click the Project from the Solution Explorer or from the Project Menu, select Import and choose Database
• Choose Select Connection from the window

• Choose the Database from the Browse tab, if we are creating for the first time.

• Set Credentials and Choose the Database from Database Name option, which we needs to create as SQL Server Database Project in Visual Studio and click Connect Button
• Choose / Tick the required Import Settings and click Start Button

• Choose Finish from the Import Summary window as show below

• All the Database scripts are imported into our new project, and now we can add or alter scripts from here or else we can make CI/CD pipeline to our new SQL Server Project (Link a SQL Server Database Project to a Git Repository).

Publish Database Script from Visual Studio

• To publish or to create the replica of the Database project which we imported to the solution, Right Click the on Project and choose Publish

• By Clicking the Edit button (as shown in the above), we can select the Server and Database where we have to publish the scripts as shown below.

• From the Database name option, we can name our new Database and Click Generate Script button.
• Once after the successful creation we get message as shown below.

• Run the Script and our new database will be created

• Now, lets check the newly created Database using SQL Server Management Studio (SSMS) or Azure Data Studio.

• All the objects are created as in the Visual Studio SQL Server Database Project

Conclusion

Managing our database now becomes easier using SQL Server Database Project. Now we can start managing the database from the Visual Studio and make CI/CD pipeline. Let’s discuss about the CI/CD pipeline in the next article (Link a SQL Server Database Project to a Git Repository). I hope you all found this article useful. Please share your feedback in the comment section.

Consider reading other SQL articles of Mine

• Customize Azure Data Studio with Dashboard Widgets
• Cycle Clipboard Ring In SSMS – Reuse Copied Items
• Set custom colors to differentiate between environments in SSMS
• Recover unsaved SQL queries in SSMS
• Multiple Backup Files of the SQL Server database with SSMS and T-SQL
• Difference between CURRENT_TIMESTAMP vs GETDATE() vs SYSDATETIME() vs GETUTCDATE() in SQL Server
• Change Schema Name of Table in MS SQL Server
• Identifying object dependencies in SQL Server using SP_DEPENDS
• Select Names Starting With Vowels in MS SQL Server
• Display Line Numbers in a SQL Server Management Studio Query Window (SSMS)