SchemaSmith Documentation
Rollback & Recovery - SQL Server (Enterprise)
Revert to any prior release state—schema changes apply automatically.
Revert to any prior release state—schema changes apply automatically.
With state-based deployments, rolling back to a prior release is straightforward: deploy the previous release's product definition. SchemaQuench compares the target state to the current database and applies the necessary changes to bring the database back to that prior state.
Unlike changelog-driven tools that require pre-planned rollback tags and manually-written rollback scripts, SchemaSmith's declarative approach means your schema metadata already describes what each release should look like. Rolling back is simply a matter of pointing SchemaQuench at an earlier version of that metadata.
When you deploy a prior release's metadata, SchemaQuench automatically reverts all schema objects to match that state.
When you roll back stored procedures, functions, and views, SchemaSmith reapplies the prior version from your metadata. This is a significant advantage over changelog-driven tools where stored logic rollback requires manually maintaining previous versions in custom rollback scripts.
While schema structure reverts automatically, data preservation for destructive operations requires planning. SchemaSmith handles the schema; you handle the data when needed.
| Scenario | What You Need |
|---|---|
| Dropping columns with data | If a column was added in the newer release and contains data you want to preserve during rollback, write a migration script to copy that data before SchemaQuench drops the column. |
| Restoring dropped columns | If the newer release dropped a column and you want to restore the data that was in it, SchemaSmith recreates the column structure but the original data is gone. You'll need a backup or migration script that preserved the data before the original drop. |
| Table drop with data | For table-level data preservation, Enterprise offers custom drop/restore hooks that let you implement a recycle bin pattern instead of immediate drops. |
| Data transformations | Complex data migrations (splitting columns, merging tables, etc.) require migration scripts to transform data appropriately during rollback. |
Check out the tagged release from source control, or retrieve the product definition artifact from your prior deployment.
If needed, compare the prior metadata to the current database state to understand what SchemaQuench will modify.
If the rollback will drop columns containing data you need, or if you need to restore data in columns that were previously dropped, run your migration scripts first.
Ensure your appsettings.json points to the correct servers and databases.
Execute SchemaQuench with the prior release's product definition:
SchemaQuenchReview the SchemaQuench logs to confirm all changes applied successfully.
Before rolling back production, test the rollback in a non-production environment to verify the process and identify any data preservation needs.
Enterprise includes hooks that let you intercept table drops and implement custom handling such as a recycle bin pattern. Instead of immediately dropping tables removed from your product definition, SchemaQuench can call your custom procedures.
SchemaSmith.CustomTableDrop
When SchemaQuench detects a table should be dropped (it was in the product
but has been removed), it checks for this stored procedure. If it exists,
SchemaQuench calls it instead of executing DROP TABLE.
Parameters:
@schema - The table's schema name@name - The table nameSchemaSmith.CustomTableRestoreNear the beginning of the quench process, SchemaQuench attempts to restore tables that were previously custom-dropped. If this procedure exists, it's called before adding new tables.
Parameters:
@schema - The table's schema name@name - The table nameA common use case is moving dropped tables to a "recycle" schema for a retention period:
recycle schema instead of dropping itrecycle and move it backrecycle after 30 daysThis pattern gives you a safety net for accidental table removals while still allowing SchemaSmith to manage your schema declaratively.
For more details on custom hooks, see Forge Deeper with SchemaQuench and Customize and Configure.
Keep tagged releases of your product definition in Git. This makes it easy to retrieve any prior version for rollback without searching through deployment artifacts.
When adding columns or tables that will contain important data, document whether rollback requires data preservation scripts. Include this in your release notes.
Practice rollback procedures in dev or staging on a regular cadence. When you need to roll back production, you'll already know the process works.
For tables where accidental data loss would be costly, implement the custom drop/restore hooks to add a safety buffer before permanent deletion.
Also available for PostgreSQL and MySQL