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)
Fig.1 Open Visual Studio
  • Click Create a new project
  • Choose Query Language from the Language dropdown and choose SQL Server Database Project as project template
Fig.2 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
Fig.3 Configure the Project
  • Once after the configuration, this is how the SQL Server Project looks (initial look).
Fig.4 Initial look of SQL Server Database Project
  • Right Click the Project from the Solution Explorer or from the Project Menu, select Import and choose Database
  • Choose Select Connection from the window
Fig.5 Import Database
  • Choose the Database from the Browse tab, if we are creating for the first time.
Fig.6 Set Credential and Choose the DB
  • 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
Fig.7 Import Settings
  • Choose Finish from the Import Summary window as show below
Fig.8 Import Summary
Fig.9 Script Files of Imported Database

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
Fig.10 Choose Server and Database
  • 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.
Fig.11 Give Database name that to be created
  • 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.
Fig.12 Successful Creation of New Database from Visual Studio
  • Run the Script and our new database will be created
Fig.13 Run the Script to Create New Database
  • Now, lets check the newly created Database using SQL Server Management Studio (SSMS) or Azure Data Studio.
Fig.14 New Database is Created
  • All the objects are created as in the Visual Studio SQL Server Database Project
Fig. 15 Objects in the Database

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

<span>%d</span> bloggers like this: