Database Change Scripts
Database change scripts are a special type of file that is run against a
database to make changes to the data structures (tables) and the data. As a
result of the fundamental nature of data and databases, a change script is
For example, a change script might remove a column to a table. In addition to
permanently altering the table's structure, the data contained within the column
are lost and cannot be retrieved. Obviously, a database may be restored from
backup, but there may be additional changes to the data or structure that is not
As a result of this unique nature, two cardinal rules of database change
- Scripts can be run once and only once.
- Script executions cannot be undone.
While not a cardinal rule, best practices dictate that each changes script
should be run through each environment (just as source code changes would be) to
ensure proper testing and verification of changes.
Note that database object code (stored procedure, views, etc) are not
considered change scripts.
Database Change Script Development Process
The unique nature of database change scripts require that a slightly
different process be used for development. Unlike code files, versioning
database change scripts is pointless. Because each modification of a change
script creates an entirely different change script, it's more important to
control the execution of each change script in each environment.
This responsibility often falls towards the Database Administrator, who
ensures that the cardinal rules are followed and rejects failed database change
scripts, advising the developer to create a new one.
Database Change Scripts in BuildMaster
Through the database change script module, BuildMaster implements both
cardinal rules and also tracks the executions of database change scripts.
Database Change Scripts in BuildMaster are associated with a deployable and a release, and track whenever they have
been executed against a database. As a result of the immutable nature of data
change scripts, modification of a script after it has been executed in an
environment is not permitted.
If a database change script is deemed unacceptable (e.g. it failed in the
first environment, or it was an incorrect change, etc), it may be set inactive
(soft deleted) or permanently purged.
BuildMaster can support virtually any relational database engine through the
use of database
providers. Once a database provider has been configured, database change
scripts may execute against it.
Change script executions are tracked through a light-weight table which is
created after a database provider has been initialized within BuildMaster.
Adding a new Change Script
Change scripts can be uploaded into BuildMaster from the listing page.
Change Script Listing
Uploaded change scripts can accessed from the SQL Change Scripts link
under the Configuration and Databases menu.
Permitted users may also download all the change scripts associated
with a specified application.
Change Script Execution
Change script executions can be performed in one of two ways: either by
manually executing the script in a specified environment, or utilizing the
built-in Execute Change Scripts action during a deployment plan execution.
- Action-based executions are used when promoting builds into new
environments. The Action will check for any change scripts that were not run
in the Environment associated with the Deployment Plan, and will run any
scripts that have not yet been run automatically.
- Manual execution of a database change script is possible, though
should limited to exceptional circumstances such as emergency data fixes; like
action-based execution, manual execution will prevent the same script from
running against the same database twice.
Action-Based Change Script Executions
A script may also be executed as part of a deployment plan.
A script can be executed from the overview page.
BuildMaster logs all change script executions and stores data pertinent to
the particular script: the Release Number associated with the script, the
Environment in which the script was executed, the date and time of execution,
and whether or not the execution was successful.
Database Change History
In addition to BuildMaster's record, databases store which change scripts
have been executed against them. This is important, as a production database may
be restored in a development environment, and therefore would not have the
latest change scripts applied.
This content has the following tags: