Ad-hoc data change scripts

Sometimes an application causes data to get into an odd state, but the bug can be hard to reproduce and the priority to fix the bug might be low. However, the data still needs to be fixed. It might only be one record in one environment in one database, and it doesn’t make sense to send a script to fix the data through the standard automated database deployment pipeline.

The majority of the time, the fix is a manual process and varies from company to company. It could be as simple as emailing the script to a DBA to as complicated as submitting a lengthly request form.

Just like database deployments, it is possible to automate this. Automation has multiple advantages over a manual process.

1. A consistent set of business rules can be applied. For example, no schema changes, and only insert or update statements are allowed.
2. The script can run through an auto-approval script. The auto-approval ensures the rules are followed. It can also run the script in a transaction and roll it back. If the script changes more than a set number of rows, for instance 10, then a DBA must look at it.
3. An automated process is faster, and it frees up the people running those scripts to do more meaningful work.
4. The process can also send out notifications with an audit trail that is easier to search through than email.

Leveraging runbooks for ad-hoc data change scripts

Runbooks were added to Octopus Deploy in version: 2019.11.

Runbooks provide an excellent way to run ad-hoc data change scripts. Runbooks don’t require a release to be created, but they still have the same functionality as a typical Octopus Deployment, such as prompted variables and auditing. Typically we find this process is a good starting point:

1. The runbook run is created, and the script to run and the database information is provided via prompted variables.
2. The script to run is analyzed for any schema change commands, and it is run and immediately rolled back in a transaction.
   1. If no schema change commands are found, the script ran successfully, and it updated less than X number of rows then a DBA Approval Required output variable is set to False.
   2. If any of those conditions fail, then the DBA Approval Required output variable is set to True.
3. Notify the approvers when that DBA Approval Required output variable is True using run conditions.
4. Pause for a manual intervention when that DBA Approval Required output variable is True using run conditions.
5. Run the script on the desired database.
6. Notify the DBAs and the person who submitted the script that the script has finished running.

For the example process, only the database name and script are prompted variables. The prompted variables allow a person to enter values prior to running the runbook. In this example, a create table command is also included in the data changes:

The auto-approval script leverages the write highlight command so important messages are shown on the task summary screen. The create table command was detected, requiring a DBA to approve the script. The DBA has a choice to accept the script or reject it. There are some cases when a create table is necessary, for example, creating a temporary table:

In another example, the same script, without the create table command is submitted. This time it passes the auto-approval and is immediately executed:

You can view this example on our samples instance.

Help us continuously improve

Please let us know if you have any feedback about this page.

Send feedback

Page updated on Sunday, January 1, 2023