Database build artefacts: build script, dacpac or .bak file?

• knightwisp

  SSC Veteran

  Points: 242

  More actions

  August 15, 2022 at 2:17 pm

  #4076542

  Curious to know what everybody's preferred build artefacts are for databases? Say you have your databases represented as scripts in source control, what deployable item is that packaged into?

  I prefer collating all source-controlled scripts into a single, dependency-ordered, idempotent, build script. Then using that for CI, unit tests. And deploying a specified subset of those scripts - updated files - as a release.

  I've seen dacpacs used but, from my own experience, I've found them complicated, risky and far too mysterious. I've also seen others produce backups which are okay for provisioning a database from scratch but don't seem to work for incremental updates to the database.

• Jeff Moden

  SSC Guru

  Points: 1004544

  More actions

  August 15, 2022 at 6:59 pm

  #4076665

  For me and just IMHO...

  If it's a brand new database, I like to deploy using a restore.

  If it's updates to a previously deployed database, then I like scripts because you can fix those in the field after you've let the "mothership" know what went wrong.

  If it's to update a table with lots of rows, then I prefer to do a restore of a copy of the table to a working database and then transfer from there although a proper file with a BULK INSERT script will do fine, as well.

  The key is... make it so someone can fix something in the field or quickly receive a file that has the fix.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)