When contingency planning for your system deployments, it's important to consider how you might handle a scenario in which a rollback is required.

However, any decision that is made needs to take into consideration the different types of assets being deployed:

• With application code, rolling back to an earlier version is pretty straight forward: you can simply replace the deployed artifacts with the previous working version
• Database rollbacks are hard due to the nature of persistent storage: the data within your schema must be preserved when rolling forward to allow a prior state to be restored at a later date (if a simple db backup/restore is not an option)

In essence, how you plan to roll back in the event of release failure entirely depends on the changes made to both your application code and database.

Assessing rollback complexity

In his blog post on this subject, Paul Stovell uses the example of a column rename to illustrate the potential complexity you might face with handling database rollbacks.

Certainly a tool can assist with providing a Down script to revert a column back to its original name (ie. alongside the Up script that performed the rename to begin with), however this adds another software asset that must be peer reviewed and tested prior to Production deployment.

Presuming that your team has the capacity to test both Up and Down scenarios for all of your migrations, imagine the scenario where you have a migration that drops a column during deployment. If you’re dealing with a very large table, your deployment could potentially take hours because your Up script would need to first take a copy of the data, so that your Down script could recover it in the event of a rollback.

And if you do decide to rollback, what if you only want to revert a subset of deployed changes? Will you know in advance how to organize your migrations so that individual changes can be cherry picked for removal, without getting tripped up by inter-object dependencies?

And how do you close-the-loop in source control to make sure your database doesn’t drift from your trunkbaseline?

The questions seem endless.

A rollback alternative: focus on moving forward

When contingency planning for your deployments, in consideration of the broader goal of maintaining compatibility between application and database, perhaps a better question to ask might be: How do I release/rollback new versions of my app while mitigating the risk of data loss?

Rather than investing time and energy into rollback planning, an alternative is to follow an approach that keeps you moving forward, like the deprecating schema with transition period technique from Pramod Sadalage and Peter Schuh (2002).

 
Source: Scott W. Amber & Associates

Michelle Steel summarises the benefits of this technique nicely:

Instead of having to manage a load of backward migration scripts in addition to the forward ones, you have transition period where both schemas exist together. At some defined point in the future, the old schema is deprecated. This gives time to bed all applications.

(Just a side note: when we talk about schema we don’t necessarily mean the entire schema, eg. [dbo]. This can mean simply preserving the structural veneer of the specific objects included in the migration.)

However, as Michelle rightly points out, it does take a certain level of commitment from a project/team perspective to ensure that obsolete objects are removed in subsequent releases.

Additionally, techniques that employ views or triggers to enforce system behaviour can carry a level of operational overhead of their own.

Create a deployment pipeline

In the spirit of Continuous Delivery, consider putting in place a process that encourages frequent, incremental change to your systems, rather than falling into the trap of doing “big bang” releases.

Big releases tend to heighten the anxiety around deployments, prompting the call for heavy-handed rollback plans to be implemented.

A good way to mitigate the risk of deployment failure is to test your release in full via one or more intermediate environments, eg.

Dev → Test → Staging → Production

Tooling provides one piece of the puzzle, but only a team working closely together can make a process work.

Keep focused on removing the friction in your processes so that moving forward becomes the default option in any of your contingency plans.

Rollback support in SQL Change Automation

Given the complexities associated with reversing changes that result in data-loss, combined with the possibility of data having changed in the target environment since the release was performed, it is unlikely that a process could be implemented that would enable the instant rolling-back of schema changes in a live production environment. However, in understanding that it is often a requirement within organizations to have a contingency plan in place prior to deployment, SQL Change Automation does provide some functionality that can support your efforts to prepare for the scenario in which a rollback is needed.

Method A – Create Down scripts up-front

This method involves using the SQL Change Automation tool-window to generate a script at development time with the revert command, allowing the script to be set aside as a contingency in the event that a rollback is subsequently required. This approach may require more effort during the development cycle, however it ensures that for every up migration that changes the state of your database, there is also a script that can be used to return the database back to the previous state. Additionally, it also means that any nuances about how the database was changed can be captured in the down script at the same time that the up script is written.

Creating the down script

These steps generate both the script to go up (i.e. a migration script) plus the script to go down, as part of the connected change-authoring workflow:

1. Add a folder to the root of the project that will contain the down scripts in the project root (e.g. /Rollback)
2. Make a change in the connected database
3. Within the SQL Change Automation tool-window, refresh the change list
4. Use the control or shift key to multi-select one or more pending objects in the list, right-click and View Revert Script. When the popup appears, copy the displayed script to the clipboard

5. Import the changes to generate the migration (ensuring that you only select the checkboxes for the items you selected in step 4)

6. Rename the migration to describe the change being performed (e.g. /Migrations/002_Drop_Customer_Column.sql )
7. Using the Solution Explorer, add a new script within the /Rollback folder with a similar name to the migration (e.g. /Rollback/002_Drop_Customer_Column_DOWN.sql )
8. Paste the script contents from the clipboard
   

Testing the down script

To test that the down script works as expected, simply execute it within the document editor. After checking the schema/data to see if the change was rolled-back as expected, execute the corresponding up script (migration) to return to the current project state. Or alternatively, refresh the SQL Change Automation tool-window, right-click the pending changes and revert the objects to return to the current project state.

Alternatively, if you'd prefer to test the script without affecting your target database, you can test your down script by validating it against the shadow database instead:

1. Using the Solution Explorer, copy the "down" script to the /Migrations folder
2. Open the script and click the Mark As Deployed button within the script status information window. 
   If the button does not appear, reset the script status by deleting the <Migration/> metadata in the header of the script, save the script, then click Insert Metadata and save again
3. (Optional step) Copy the contents of the "up" script, paste it onto the end of the down script and save
   This optional step includes the up script as part of the test in order to make it easier to confirm that the database state is being modified as intended (more about this in step 5)
4. Within the SQL Change Automation tool-window, click the Refresh button to apply the script to the shadow database.
   If any errors occur during the execution of the script, they will be shown within the tool-window (click Open Log to display full details of any SQL Server errors)
5. Make any adjustments to the script as needed, and click Refresh to try the verification again. If the script executes successfully then the window will return to the list of pending changes
   If step 3 was performed, then all objects should be shown as "Identical" (i.e. there will be no objects pending changes import into the project)
   If step 3 wasn't performed, use the SQL Server Object Explorer to browse to the server and confirm that the shadow database (e.g. MyProject_[username]_SHADOW) is in the expected state
6. Delete the script (added in step 1) from the /Migrations folder

Performing a rollback using down script(s)

In the event that you need roll back changes that were introduced by your project's up scripts, we recommend simply moving one or more of the down scripts into the Migrations folder so that the down scripts can be run as part of your existing deployment pipeline.

Preparing to roll back begins by editing your SQL Change Automation project within Visual Studio:

1. In your source control client, update your workspace from the branch that was deployed to your target environment (typically "master")

   The next few steps are necessary in order to ensure that the /Programmable-Object folder structure is also included in the rollback. If you are not using the programmable object feature, or if you do not wish to rollback stored procedures, functions or views, skip to step 6

2. Locate all of the up script(s) that were deployed in the release and copy them to a folder outside of your source control workspace

3. Locate the corresponding down script(s) that you wish to roll back, and copy them to a folder outside of your source control workspace

4. In your source control client, update your workspace from a tag or branch corresponding to the pre-deployment state of your database (this may involve reverting commits from the master branch)

5. Using the Solution Explorer, add the up scripts (from step 2) to the /Migrations folder of your SQL Change Automation project

6. Using the Solution Explorer, add the down scripts (from step 3) to the /Migrations folder of your SQL Change Automation project
7. Adjust the numeric prefix of the down script(s) so that they run after the up scripts (i.e. max script number + 1)
   If multiple down scripts are required to perform the rollback, then it may be necessary to reverse the order of deployment of the scripts in order to account for any dependencies between them
8. Test the down script(s) against your local database by deploying the project/solution
9. Validate the up and down scripts together by clicking the Refresh button within the SQL Change Automation tool-window
   This will also update the /Schema-Model folder with the current database state (if the offline schema model feature is enabled)
10. Commit the project modifications to source control, and merge to the appropriate baseline in source control (e.g. master)

Once the project has been built, you can then deploy the database in the normal way (e.g. using Azure DevOps, Octopus Deploy, PowerShell etc) to perform the rollback.

Method B – Create a Down script when a rollback is required

This method also involves using the revert feature within the SQL Change Automation tool-window to generate a script. However the difference with this approach is that the script is created at the time that a rollback is actually needed, rather than at the time that the up (migration) script is originally authored. The advantage of this approach is that it can save time during development, as the creation of the down script is a once-off task that would be performed only if a rollback is required. The disadvantage of this approach is that it requires more work to be done at a potentially critical stage of a software development project – the release to Production itself – which may result in longer than expected system downtime. Furthermore, given that the down script is generated after-the-fact, important details about how the change was originally made (which could have occurred much earlier in the development cycle) may not be taken into consideration by the developer/release manager.