16

In Sql Server Data Tools for Visual Studio, you can create a Sql Server Project and import the structure of a Database. This works very well.

Is it also possible to import data as well? For example, lets say I have a Type table with several types. I would like to have this data in the Sql Server Project, so when I publish it, it publishes the data as well.

The closest I have been able to figure out is to use Sql Server Object Explorer to create a script for the data and then manually add that script to the project.

When I saw the demo of Sql Server Data Tools, then showed publishing a project, but then using copy and paste to get data into the database. Surely there is a better way.

EDIT Years later, I finally figure this out: There are two types of exports: 1. DACPAK - which includes only the structure, 2. The BACBAK which includes data and structure.

You can created either from SSMS: 1. DACPAK: Select your database, right click for Tasks-> Extract Data Tier App. 2. Select database, right click for Tasks-> Export Data Tier App.

Greg Gum
  • 33,478
  • 39
  • 162
  • 233
  • Well, no comments, no replies, a tumbleweed. I have not found any further data on this. If I do, will post here. – Greg Gum Nov 10 '13 at 17:41
  • http://stackoverflow.com/questions/22746200/how-to-include-custom-data-migrations-and-static-reference-data-in-an-ssdt-proje perhaps? – DonBecker Sep 10 '14 at 16:47

2 Answers2

13

Years later, I finally figure this out: There are two types of exports:

  1. DACPAC - which includes only the structure
  2. BACPAC which includes data and structure.

You can create either from SSMS:

  1. DACPAC: Select your database, right click for Tasks-> Extract Data Tier App.
  2. BACPAC: Select database, right click for Tasks-> Export Data Tier App.
Josh Gallagher
  • 5,211
  • 2
  • 33
  • 60
Greg Gum
  • 33,478
  • 39
  • 162
  • 233
  • 4
    Good day Greg, Extracting DACPAC with data is available from 2012 (search for post written by Adam Mahood from Microsoft under the Official team blog for SSDT). The main different is that DACPAK is like a database snapshot file and it can be used for update while BACBAK is more of a backup and is used for creating new database. – Ronen Ariely Jan 05 '18 at 05:47
  • 1
    Here is a working example of creating DACPAC with data of the table named T: sqlpackage.exe /action:Extract /TargetFile:"E:\test.dacpac" /sourceDatabasename:RonenDB /sourceservername:.\RonenServer /p:TableData=dbo.T – Ronen Ariely Jan 05 '18 at 05:51
  • One more difference is that a BACPAC can not be created when there are sql logins in the database. In that case a DACPAC including data should be used. – H. de Jonge Aug 31 '18 at 10:44
12

This isn't exactly from Visual Studio as you asked but it might be a workaround. If your db exists in a dev server then maybe you can use SqlPackage.exe Export and Extract actions:

For Export

By default, data for all tables will be included in the .bacpac file.

A SqlPackage.exe Export action exports a live database from SQL Server or Windows Azure SQL Database to a BACPAC package (.bacpac file). By default, data for all tables will be included in the .bacpac file. Optionally, you can specify only a subset of tables for which to export data. Validation for the Export action ensures Windows Azure SQL Database compatibility for the complete targeted database even if a subset of tables is specified for the export.

sqlpackage.exe /action:Export /TargetFile:"test.bacpac" 
    /sourceDatabasename:test 
    /sourceservername:.\testserver

You can import your bacpac file https://msdn.microsoft.com/en-us/library/hh710052.aspx

For Extract

Makes a DACPAC with the user table data

sqlpackage.exe /action:Extract /TargetFile:"test.dacpac" 
    /sourceDatabasename:test 
    /sourceservername:".\testserver"
    /p:ExtractAllTableData=true

If you don't want to use SqlPackage.exe, this article is old but it has three workarounds that might work:

1) Redeploy the same .dacpac file using SSMS's "Upgrade Data-tier application..." wizard under the [Server]/Management/Data-tier Application/[Application Name] node in the SSMS Object Explorer. The upgrade wizard presents presents checkbox options to execute the pre- and/or post-deployment scripts in the .dacpac file. Enabling both the pre- and post-deployment script options, then performing the upgrade will produce the expected result.

2) Manually execute the DACPAC pre- and/or post-deployment T-SQL script files using a SSMS query window, SQLCMD.exe, or simliar. This requires the DACPAC author to ship the pre-/post-deployment scripts alongside the DACPAC file. Alternatively, the pre-/post-deployment scripts can be extracted using the Microsoft DacUnpack.exe utility, or a ZIP file utility (after renaming the file extension from .dacpac to .zip).

3) Use either MSBuild.exe (v4.0.30319.1, or higher) or Visual Studio 2010 Premium SP1 (or higher) to deploy the "SQL Server Data-tier Application" project file. (Exampe: "msbuild /Target:Deploy DeploymentDemo.dbproj").

Martin Mulder
  • 12,642
  • 3
  • 25
  • 54
Heinrich
  • 1,711
  • 5
  • 28
  • 61
  • Typo: `/p::ExtractAllTableData=true` should be `/p:ExtractAllTableData=true` – Zero3 Aug 22 '19 at 14:38
  • Is there a way to insert/update a new set of data to a particular table in the existing database using dacpac ? – Varun Mar 09 '23 at 07:16