How to Create SQL Server Database Project With Visual Studio

Introduction

In this article, we are going to learn about SQL Server Database Project (Template) available in Visual Studio. I will cover the following points:

• SQL Server Database Project - Introduction
• Create New SQL Server Database Project
• Import database schema from an existing database, a .sql script file or a Data-tier application (.bacpac) the Project
• Publish to create new Database in SQL server

Background

The database plays a most important role in any application and it becomes difficult to manage the project when the number of tables, views, stored procedures increase.

Consider scenarios where multiple developers are working on a project for the next release, some are working on bugs or adding new features which again requires some or many changes in the database. Most of the time, developers take note of DB (Database) changes manually. Sometimes, they miss some changes which cost in production. There are many cases that Dev, UAT and production DB are different - it is again a hassle to identify the differences. There are a number of tools available in the market for comparing DBs but they are costly or paid solution. So, in this article, we will discuss and learn about SQL Server Database Project which is available in Visual Studio which is free. Yes — Free!!

Prerequisites: Visual Studio (2013, 2015, 2017, or 2019) and MS SQL Server. I am using VS2019 and SQL Server 2017 Developer for illustration.

Introduction

You can create a new database project and import database schema from an existing database, a .sql script file or a Data-tier application (.bacpac). You 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 pane, you can change the target platform to different versions of SQL Server (including SQL Azure). (copied MS Docs)

Create New SQL Server Database Project

1. Open Visual Studio and create a blank solution.

   

2. Add a Project.

   

3. Select SQL Server from Left panel and SQL Server Database Project. Give a name to the project (here, I am giving name as SampleAccount). This template is used for creating a SQL server database.

   

   Import database schema from an existing database, a .sql script file or a Data-tier application (.dacpac) the Project

4. The next step is to import database scheme from an existing database. Import database schema from an existing database, a .sql script file or a Data-tier application (.dacpac) the Project. So, right click on the project and select import. There will be three options: Data-Tier Application (.dacpac), Database, Script (.sql). Here, I am using the Database option because I will import from MS SQL database, as portrayed below.

   

5. Now you will see the screen as below. This option is available to provide connection string. There are options to select database from local, network or Azure, so based on your database, you can choose the option. Alternatively, you can enter server name, authentication type and credentials to connect directly to database server. And then choose the database which you want to import and keep in your project.

   

6. Next, we have to set import settings. There are some options:
   • Import application-scoped objects: This will import tables, views, stored procedures likewise objects
   • Imports reference logins: Login related imports
   • Import Permissions: This will import related permissions
   • Import database settings: This will import database settings
   • Folder Structure: Option to choose folder structure in your project for database objects
   • Maximum files per folder: Limit number files per folder

   In my case, I selected the following options as depicted.

   

7. Click Start which will show the progress window as:

   

After Finish, we will see the tables, views and stored procedures in our project.

Finally, we have successfully created SQL server Database Project. Now we can publish and create new dataset from solution itself using this project. Now, I will show how to publish and create new database in SQL server from this SQL server Database Project.

Publish to Create New Database in SQL Server

We can easily create a new database directly from Visual Studio using the SQL Sever Database Project with the same schema. Let us proceed.

1. Right click on the project and choose the publish option.

   

2. Provide Connection: Server name, Authentication Type, credentials. If we want to publish as new database, then choose default database, or choose specific database to publish.

   

   

3. We can generate script or publish directly. You can explore Advanced options as well to apply rules on publish.

   Let’s explore Advanced options with more rules and options. Options are: Deployment behaviors

   • Deploy database properties
   • Always re-create database
   • Block incremental deployment if data loss might occurs
   • Execute deployment script in single-user mode
   • Backup up database before deployment
   • Do not user alter assembly statements to update CLR types

   

   Additionally, there are several advanced deployment options like: compare using target collation, disable and reenable DDL triggers, do not alter change data capture objects, do not alter replicated objects, drop statistics not source, include composite objects, include transactional scripts, populate files of fileGroups likewise several options as shown. Then, click OK in advanced options and publish the database.

   Now our new database is created or generated script can be used to create.

   

   Now, your database is created in SQL server, you can login to SQL server and check as shown. Finally, we created a database from Visual Studio, SQL server Database project successfully.

Conclusion

In this article, I have shown how to create SQL server database project in Visual Studio and import the SQL database. We can import development database and finally publish into UAT, production or another server from Visual Studio itself with this project template. Additionally, we have explored some advanced options during imports and publishing of the database. There are some additional useful options or use cases we have with this project like to compare and identify the differences in two databases (development and production), then we can generate updated script or update directly from the project. I will explain more about comparing and updating in the next article.

History

• 4^th April, 2021: Initial version