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

  • 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.

  • 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)
    Intro to Tally Tables and Functions

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply