A different approach to managing schema changes is to not keep track of the individual changes (or migrations), but only store the latest version of the schema in source control. External tools are then used to compare the current schema in source control with the actual schema of the database, generate migration scripts, and apply these when running. The migration scripts are not stored, and are single-use only.

Unlike writing migrations, it is not feasible to execute a task like this by hand. While tracking the newest version of the schema by hand in source control can be managed, the same is not feasible for an end-state approach. Generating a migration script while comparing the existing schema and the new schema and applying this migration script can only be done using a tool. Examples of these tools are Redgate SQL Source Control and SQL Server Data Tools. How these tools work, is shown in the here:

Here we see how the current actual database schema and the description of the desired database schema are compared to generate an upgrade and directly apply a script for making the changes needed to make the actual schema the same as the desired schema.

One advantage of this approach is that there is no series of scripts generated that have to be executed in a specific order. Therefore, this approach combines easily with extensive branching schemas, where changes are integrated more slowly over time. It also removes the need to write migrations by hand for simple scenarios, such as adding or deleting a column, table, or index.

The disadvantage of this approach is that it makes it harder to handle changes that need data operations as well. Again, imagine a scenario of moving two columns to another table. Since the tooling only enforces the new schema, this will lead to data loss if there is no further intervention.

One possible form of intervention to circumvent this is the addition of pre- and post-deployment scripts to the schema package. In the pre-deployment script, the current data is staged in a temporary table. Then, after applying the new schema, the data is copied from the temporary table to the new location in the post-deployment script.

This section was about managing database schema changes in a format that can be stored in source control. The next section discusses how these changes can be picked up at deploy time and then applied to a database.