Using DACPACS to version Database

Question

Hi All,

I was hoping to use DACPACs as a convenient way of both upgrading databases and versioning them.

If I use a DACPAC with a version number added during it's generation and use it to upgrade the data-tier application, a subsequent select query from [sysdac_instances] shows the [type_version] number has been updated. However, if I subsequently detach the database and attach a copy of it that has not been through the upgrade process, the same query from [sysdac_instances] gives me the same version number.

Thus the DACPAC version number is a function of the server and not the database. If I am attaching and detaching databases with the same name I have no easy way to figure out which have been upgraded and which haven't.

Am I missing something?

Thanks,

Chris

Answer 1

Hi @Chris Swainson ,

You can try to use with SSDT

There is a way to as user-defined extended properties:
https://dba.stackexchange.com/questions/131018/howto-create-useful-dacpac-versioning-along-with-ssdt-deployment

https://www.techrepublic.com/article/auto-deploy-and-version-your-sql-server-database-with-ssdt/

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The best thing to do is to create an "extended property" on the database with your version in it. Then it goes with the database.

See:
https://www.mssqltips.com/sqlservertip/5384/working-with-sql-server-extended-properties/

Answer 3

Thanks for your swift replies.

I'll try the proposed solution using extended properties.