Database Project Tutorial Using Visual Studio 2012

5.1 Code Analysis

Click on SQL Menu > Static Code Analysis > Run to check for any errors or warning and fix it by clicking on the list shown.

This will keep the code clean from errors.

5.2 Build Project

Once you are done with all the creation and changes in Database Project, right-click the Project folder and Build.



Once the build is successful we are ready to create a publish profile.

Before creating it, check if the Database Project SQL Server Target Platform version matches your SQL Server Version.

5.3 Setting Right Database Target

Right-click the Project folder then select Properties and correctly set the SQL Server Version on the Target.

Also Advanced Database Settings can be done by clicking on Database Settings below.



5.4 Create a Publish Profile

Publish the Profile .xml file that contains a collection of all the property key-value pairs necessary to deploy a database model (a .dacpac) to a target database.

Right-click the Project folder then select Publish then do the following:


1. Enter the Database Name. With this name the database will be created.
2. Click Edit to open the Connection Properties window.
3. Select your Server.
4. Save the Profile.
5. Click Publish to publish you Database Project.

There is also an Advanced button. You can click on the Advanced button and do make more settings, like "Always Re-Create the Database", "Back-up before Deployment" and so on.



Once the Publish Profile is created you can modify by opening and editing it manually.

1. <?xml version="1.0" encoding="utf-8"?>
2. <Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
3. <PropertyGroup>
4. <IncludeCompositeObjects>True</IncludeCompositeObjects>
5. <TargetDatabaseName>SalesDemo</TargetDatabaseName>
6. <DeployScriptFileName>SalesDemo.sql</DeployScriptFileName>
7. <ProfileVersionNumber>1</ProfileVersionNumber>
8. <TargetConnectionString>Data Source=WIN-1J0QGUUL45M\SQLEXPRESS;Integrated Security=True;Pooling=False</TargetConnectionString>
9. </PropertyGroup>
10. </Project>
5.5 Checking for Publishing Errors and Status

Open Tools > Other Windows > Data Tool Operations to view the results.

If you get errors when Publishing click on View Results to fix them.

Publishing Error



Publishing Success



5.7 Check the resulting Physical Database from SQL Server Management Studio

Verify that all the database objects are properly created.



If the Database Project is kept under Source Control then when database changes happen in the future, we can simply open the Database Project, update the required database object .sql and then Build and Publish. The physical database will be updated accordingly.

This way all the changes will be under control and history maintained and the database will be in sync.

6. Importing an Existing Database to a new Database Project

If the scenario is that we already have a database with all the tables, procedures, views, functions and so on and we want to bring that into the Database Project and keep it under source control then we obviously don't want to create all the matching database objects manually in our project. That would be a tedious job.

This is want we can do.

6.1 Create a .dacpac file

Open SQL Server Management Studio then right-click on the database you to import then select Tasks >Extract Data-tier Application.

Then follow the simple procedure and a .dacpac file is created. Remember the location where the file is created.

A DACPAC file contains all of the information necessary to build the db objects specified in the SQL DB project.



6.2. Import the .dacpac file into the Database Project

Open Visual Studio and create a new Database Project.

Right-click on the project then click on Import > Data-tier Application (*.dacpac).

The "Import Data-tier Application File" dialog will open.

Select the .dacpac file you created previously and click Start.

All your database objects will be imported into your Database Project.



Watch full Video Tutorial of Creating a Database Project in VS2015