Click here to Skip to main content
15,917,862 members
Articles / Programming Languages / SQL

Creating a Database Project with Visual Studio

Rate me:
Please Sign up or sign in to vote.
3.65/5 (9 votes)
25 Aug 2011CPOL3 min read 231.4K   22   9
This article shows how to create a database project to manage database scripts in Visual Studio


Important: This kind of project can only be used with Premium or Ultimate version. It does not exist in Professional or Express edition.

First, why should you use this kind of project?

Simple! This kind of project automatically generates scripts to deploy your database. You just have to create a file for each element you want to create (table, stored procedures, triggers ...) and Visual Studio would generate a script file that will execute every query in the correct order. You can use it with SQL Server 2005, 2008 or 2008 R2.

We will see in this article how to create a Visual Studio project and deploy it to a database server.

Creating the Visual Studio Project

Open your Visual Studio and go to "New Project -> Database -> SQL Server -> SQL Server 2008 Database Project".

Creating new Database project in Visual Studio

Give a name to the project and click on the OK button.

Here is what it should look like:

View Database Project in Solution Explorer

Now, we create our first table. In the treeview, do a right click on Tables -> Add -> Table and check that Table is selected in the new window.

Add a New Table

About the filename, I simply use the table name. So, a new file opens with a template to help you create a table:

New Table Script Template

You can now modify it like this:

CREATE TABLE [dbo].[MaTableA]
Name nvarchar(256) NOT NULL,

Then we create a second table to have some realistic database schema:

CREATE TABLE [dbo].[MaTableB]
Name nvarchar(256) NOT NULL,

We can see our two scripts in the project:

Les 2 Scripts

Let's deploy our project. First, we need to configure the connection string. Right click on the project and select Properties.

MaBase Project Properties

Deployment information should be configured in Deploy tab. What do we have there?

  • Configure deployment settings: allow to specify if parameters should be used for the project or only for our current environment.
  • Deploy action: allows to determine which action should be done when we deploy: "Create deployment script" or "Create deployment script and deploy it".
  • Deployment script name: allows to specify the script name.
  • Target database settings: Here, we will configure the target database. Visual Studio will use this database to generate a script to deploy the new things only. First, we configure the connection strings, then we specify the database name.
  • Deployment configuration file: File containing deployment parameters
  • SQL command variable file

So, let's see how to configure the connection. Click on Edit on the right of Target Connection.

Connection Properties

Add server name, choose the authentication and add database name. It does not matter if the database does not exist. If Visual Studio doesn't find it, it will add the creation of the database in the deployment script. Test the connection (test failed if database does not exist) and click OK. Change deployment configuration to have the script generated and deployed.

So now, we are ready to deploy. Right click on the database project and select Deploy.

To check that everything went right, open the Output window, you should see something like that:

Deploy Output

Using the Server Explorer, we go to the database server:

DB Created

Our database is here and has the two tables we've defined. The SQL script is in the directory sql\Debug of our project. Go there to know what it looks like.

In the treeview of the project, we can also find pre and post deployment scripts. These scripts would be executed respectively before and after the script generated by the project. The first one can allow to initialize some parameters whereas the second one can allow to add data.

Here is the end of this first article. This first part is a simple approach of this kind of project. We will see more features in the second part.

Points of Interest

This kind of project is really useful for managing database script and to work in a team. Every member of a team can make changes and merge their scripts with other team members in a very useful way. And you can keep versioning on each object.


  • 25th August, 2011: First version
  • 25th August, 2011: Update: Added Visual Studio version


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
France France
I'm coding in .Net since 9 years, most with ASP.Net and SharePoint and a little using WPF, MVC, Windows Phone 8 and WinRT technology.
I have learned so much reading others experience and tutorials, or tips so I try to do the same, keeping learning from others of course.
You can also find my blog here :

Comments and Discussions

QuestionHow to Add Indexes of a Table as part of db Project Pin
srialeti13-Jan-17 7:54
srialeti13-Jan-17 7:54 
QuestionCan i get a link for 2nd part of this article Pin
Member 120355515-Oct-15 19:14
Member 120355515-Oct-15 19:14 
GeneralMy vote of 3 Pin
Member 1076371724-Nov-14 0:01
Member 1076371724-Nov-14 0:01 
GeneralMy vote of 5 Pin
Eng.Khalid Jamal27-Oct-14 9:11
Eng.Khalid Jamal27-Oct-14 9:11 
QuestionTo enable IF Exists Pin
Mayank143-Oct-13 1:42
Mayank143-Oct-13 1:42 
GeneralMy vote of 3 Pin
Petr Pechovic26-Aug-11 2:01
professionalPetr Pechovic26-Aug-11 2:01 
Questionit's not working Pin
Steganer25-Aug-11 4:09
Steganer25-Aug-11 4:09 
AnswerRe: it's not working Pin
Nadege Rouelle25-Aug-11 4:16
Nadege Rouelle25-Aug-11 4:16 
GeneralRe: it's not working Pin
Steganer25-Aug-11 5:16
Steganer25-Aug-11 5:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.