5

Can I create a DACPAC including data in SQL Server 2008?

My requirement is to generate the incremental script of the DB changes, would I be able to do that using BACPAC?

I tried reading this, but this is not much helpful:

https://msdn.microsoft.com/en-us/library/jj860455(v=vs.103).aspx

Saranya
  • 1,988
  • 16
  • 20
  • 2
    A `DACPAC` **always** contains **only** the structure of the database. If you want the data, too - you need a `BACPAC` – marc_s Feb 13 '18 at 07:42
  • my requirement is to generate the incremental script of the DB changes, would I be able to do that using BACPAC?. Also I read from this msdn url about including data in dacpac .https://msdn.microsoft.com/en-us/library/jj860455(v=vs.103).aspx – Saranya Feb 13 '18 at 11:34
  • that 'extract data tier application' will contain a 'select data' option in the wizard, which isn't available for me – Saranya Feb 13 '18 at 11:35
  • You can extract a .dacpac from a database. Extract creates a database snapshot file (.dacpac) from a live SQL Server or Windows Azure SQL Database that might contain data from user tables, in addition to the database schema - from MSDN – Saranya Feb 13 '18 at 11:36
  • According to the [official MIcrosoft docs](https://technet.microsoft.com/en-us/library/hh213241(v=sql.110).aspx) - no, this is a SQL Server **2012 and newer** only feature – marc_s Feb 13 '18 at 14:05
  • I tried using SQL 2012 as well and i still dont find the option in the extract wizard. Is there any patch that needs to be installed for this option to be available – Saranya Feb 14 '18 at 06:19
  • 3
    @Saranya, `Extract` creates a DACPAC (schema only) whereas `Export` creates a BACPAC (schema and data). A DACPAC may be used to generate an incremental deployment script to upgrade a database while retaining existing data in the target. A BACPAC can be used to create a new database with both schema and data. The [limitations state SQL Server 2005 SP4 or later is required](https://learn.microsoft.com/en-us/sql/relational-databases/data-tier-applications/extract-a-dac-from-a-database). – Dan Guzman Feb 16 '18 at 11:49
  • @Saranya are you trying to mix DB structure changes and data changes into one? That would not work well and you will not find a good solution to this. – trailmax Feb 16 '18 at 14:14
  • A BACPAC will only add data via bulk import; it will not modify existing data (through comparison or otherwise). It cannot be used to deploy incremental data modifications. The usual solution is to use pre- and post-deployment scripts for that. – Jeroen Mostert Feb 16 '18 at 14:33
  • You can use a DACPAC with pre & post deployment sql script files. The DACPAC can be used on databases with existing data, you just need to ensure you have the right defaults and constraints for new columns – Steve Ford Feb 20 '18 at 13:44

1 Answers1

2

You can use SqlPackage.exe tool manually found actions here.

Use Export action as:

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

Use Extract action as:

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

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