Can I update only the needed changes in a database? First of all, I apologize if you can't understand very well what I am saying and it can be edit for better understanding of my post. The next example is for better understanding of databases with control version.

Suppose I have a software used by several businesses. This software need a database to function correctly, that means every business have one. I have a central database using migrations and scripts to have control version but this central database do not comunicate with the others. Every business have a different version from each other, including the central database that always have the last version.



To update the databases to my last version I apply idempotent scripts (like Entity Framework) so I can detect the version and continue from there. Let say the business with the version 15.11 have an error with the database and need to be solve right away. I recreate the bug, fix it, create a script, and bring the update to the company. Now this company have a different version, like 15.11.1. and the last script it's included in the central database. 

So here comes my question, if I decide to update in the future that same database to the last version, my idempotent script do not apply this change again?

Another question is: Can I apply differents scripts in a database? i.e. if in the update are 5 scripts and I only want to apply the #1, #3 and #5. The idea is for reduce the updating time if I have change already applied.

I am open for all kind of opinions and if I have some concepts wrong, please feel free to correct me.

Thank you.
I would suggest reading this whitepaper on database version control from DBmaestro TeamWork (where I work)

Basically, it discuses how database enforced change management combines the enforcement of version control processes on the database objects with the generation of the deployment script when required based on the version control repository and the structure of the environment at that time.

This approach uses build and deploy on-demand, which means the deploy script is built (generated) when needed, not as part of development. This allows for efficient handling of conflicts, merges, and out-of-process changes.

It handles the challenges by:

Ensuring all database code is covered – structure, business logic written in the database language, reference content, database permissions, and more are managed 

Ensuring the version control repository can act as the single source of truth – the enforced change policy prevents anyone (developers, DBAs) using any IDE (even command line) from modifying database objects which were not checked-out before and checked-in after the change. This guarantees that the version control repository will always be in sync with the definition of the object at check-in time.

Ensuring the deployment script being executed is aware of the environment status when the script is executing – building (generating) the deployment script when needed (just before executing) guarantees it is aware of the current environment status.

Ensuring the deployment script handles conflicts and merges them – by using baselines in the analysis, the nature of the change is known and the correct decision whether to promote the change, protect the target (ignore the change), or merge a conflict is easy.

Generating deployment scripts for only relevant changes – the integration with application life-cycle management (ALM) and change management systems (CMS) enables you to assign a reason to the change, as is done in the file-based version control or task management system.

Ensuring the deployment script is aware of the database dependencies – the sophisticated analysis and script generation algorithm ensures the DDLs, DCLs, and DMLs will be executed in the correct order based on the database dependencies, including inter-schema dependencies.