1

I have a sql server database already containing data. I want to start versioning it. I know I can use Database project in Visual Studio, and by importing database I can generate sql scripts.

But what about data in the database? I tried to make some Data-Tier Application Files, but when I try to import it in my DB project in Visual Studio I am getting this error:

Import Data-Tier Application File - This operation is not supported for packages containing data

So how do I import data? It has to be some way, because when I am extracting DAC file there is option Extract Schema and Data so there has to be a way to use this data afterwards.

Or maybe post deployement scripts are the only option?

Grettings

jarlh
  • 42,561
  • 8
  • 45
  • 63
karollo
  • 573
  • 8
  • 22
  • [Maintaining version history](http://sqlblog.com/blogs/jamie_thomson/archive/2010/10/19/maintaining-version-history-in-your-database-using-visual-studio-2010.aspx) I believe this might be useful.You can use powershell to deploy your databases, you need to set the switches to : /p:BuildVersion=$(BuildLabel) – otri Mar 20 '19 at 10:05
  • 1
    AFAIK, post-deployment scripts are the only out-of-the-box method to version data. You could use SSDT data compare to help generate scripts. One method I've used is a post-deploy script that inserts all desired data into a temp table/variable and then a `MERGE` to update the final table accordingly. – Dan Guzman Mar 20 '19 at 10:15
  • @DanGuzman so why do you have this `Extract Schema and Data` option, when extracting DAC in object explorer? It has to be a way to use it somehow – karollo Mar 20 '19 at 10:21
  • @KarolŻurowski, that option creates a BACPAC, rather than a scheme-only DACPAC. A BACPAC can be used to import a data-tier application, creating a new database with data. – Dan Guzman Mar 20 '19 at 10:31
  • @DanGuzman I don't think you are right. In my case it created a DACPACK file. If I chose it to be schema only, I could easy import it by `Extract ..` option. If i chose schema+data, extracting generates an error – karollo Mar 20 '19 at 10:46
  • @KarolŻurowski, it looks like the DACPAC option with data included was introduced with VS 2012 (the option isn't available in SSMS). I don't believe such a DACPAC can be created directly as an SSDT project build artifact; only by reverse-engineering (extracting) an existing physical database. So you could deploy schema and data (with a post-deploy script) and then extract a new DACPAC with both. See this [related answer](https://stackoverflow.com/questions/19819681/how-to-include-data-in-a-dacpac). – Dan Guzman Mar 20 '19 at 11:29
  • @DanGuzman Ok, I get it. But how can I use this "DACPAC with both" - schema and data – karollo Mar 20 '19 at 12:40

1 Answers1

0

Your only option for this at this time is to use post-deploy scripts to populate those tables, taking into account the fact that the scripts need to be able to run multiple times without re-inserting data. A temp table/table variable and a MERGE statement are probably your best bets if you might have changes to the reference data, otherwise a left join might suffice.

Others have tried to include reference data, but it's a pretty hard problem to solve in a manner that works well for everyone. I know others like Ed Elliott have written some stuff that can turn those on/off as needed so you're not always including all reference data every time. You could also look into a post-post-deploy scenario where after your publish and post-deploy, you run a separate script that updates the data from static files. They'd still be in source control, but not necessarily part of your SSDT project. You'd have to remember to run that script in your builds, though.

I know for a while we had a database that solely had the lookup tables populated so we could reference that and do data compares if needed, but that still requires someone to maintain those values in an ongoing manner.

Peter Schott
  • 4,521
  • 21
  • 30