Reversal

DataStar components encapsulate data as scriptable units. When a deployment runs, DataStar can capture the pre-change state and generate reversal scripts that undo the change. If a release needs to be rolled back, you run those scripts to restore the database to its previous state.

Reversal works for both data components (generated automatically from the target database) and custom scripts (using metadata tags you add to the script).

Prerequisites

Audit tables

Reversal depends on three database tables that DataStar writes to during deployment. Create them in every target database before your first deployment.

SQL Server

CREATE TABLE [dbo].[ADS_DEPLOYMENT_HISTORY] (
    [ID]           [int] IDENTITY(1,1) NOT NULL,
    [WORK_ITEM]    [varchar](50)       NOT NULL,
    [VERSION]      [int]               NOT NULL,
    [ARTIFACT]     [varchar](50)       NOT NULL,
    [DATE_STAMP]   [datetime]          NOT NULL,
    [LOG_OUTPUT]   [varbinary](max)    NULL,
    [REVERSAL_ID]  [int]               NULL,
    [STATUS]       [char](1)           NULL,
    CONSTRAINT [ADS_DEPLOYMENT_HISTORY_PK] PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE [dbo].[ADS_DEPLOYMENT_REVERSAL] (
    [ID]       [int]            NOT NULL,
    [SEQUENCE] [int]            NOT NULL,
    [CATEGORY] [nvarchar](100)  NOT NULL,
    [NAME]     [nvarchar](100)  NOT NULL,
    [SCRIPT]   [varbinary](max) NULL,
    CONSTRAINT [ADS_DEPLOYMENT_REVERSAL_PK] PRIMARY KEY CLUSTERED ([ID] ASC, [SEQUENCE] ASC)
);

ALTER TABLE [dbo].[ADS_DEPLOYMENT_REVERSAL]
    ADD CONSTRAINT ADS_DEPLOYMENT_REVERSAL_FK1
    FOREIGN KEY ([ID]) REFERENCES ADS_DEPLOYMENT_HISTORY ([ID])
    ON DELETE CASCADE;

CREATE TABLE [dbo].[ADS_DEPLOYMENT_SUMMARY] (
    [ID]         [int]            NOT NULL,
    [TABLE_NAME] [nvarchar](255)  NOT NULL,
    [INSERTED]   [int]            NOT NULL,
    [UPDATED]    [int]            NOT NULL,
    [DELETED]    [int]            NOT NULL,
    CONSTRAINT [ADS_DEPLOYMENT_SUMMARY_PK] PRIMARY KEY CLUSTERED ([ID] ASC, [TABLE_NAME] ASC)
);

ALTER TABLE [dbo].[ADS_DEPLOYMENT_SUMMARY]
    ADD CONSTRAINT ADS_DEPLOYMENT_SUMMARY_FK1
    FOREIGN KEY ([ID]) REFERENCES ADS_DEPLOYMENT_HISTORY ([ID])
    ON DELETE CASCADE;

Oracle

Use equivalent Oracle syntax for the same structure. Column types map as: int → NUMBER, varchar/nvarchar → VARCHAR2, varbinary(max) → BLOB, datetime → TIMESTAMP, IDENTITY → sequence + trigger or identity column (12c+).

Configure the table names

Open Workspace → Settings → General. Under Deployment History Tables, enter the names of the three tables:

Field	Default
Audit Table	ADS_DEPLOYMENT_HISTORY
Reversal Table	ADS_DEPLOYMENT_REVERSAL
Summary Table	ADS_DEPLOYMENT_SUMMARY

You can use different names if your DBA requires it; just make sure they match the tables you created.

Configure the reversal location

Open Workspace → Settings → Paths & Filters. The Reversal Location is the workspace-relative path where reversal packages are written when generating to disk (as opposed to storing in the audit tables).

How it works

When you deploy with reversal enabled:

1. Before each component runs, DataStar pulls the current state from the target database.
2. The component script executes, making its changes.
3. DataStar generates a reversal script from the captured pre-change state. For data components this is automatic; for custom scripts you control it with metadata tags (see below).
4. The reversal script is stored: either in the audit tables or as a file in the reversal location, depending on your settings.

To undo the deployment later, you run the stored reversal scripts against the same database. This restores the data to the state it was in immediately before the original deployment.

Deploying with reversal

From the client

When you click Deploy on a deployment file, the Run Script dialog includes a Reversal Mode setting:

Mode	What it does
None	No reversal scripts are generated.
Package	Reversal scripts are generated and stored as a package (in the audit tables or a NuGet package).
Directory	Reversal scripts are written to the configured reversal location on disk.

Select COMMIT mode and the reversal mode you want, then click Run.

From the CLI

Pass -re to enable reversal generation during deployment:

DataStar.Tools.exe -db "Microsoft" \
  -cs "your-connection-string" \
  -ae -ah "ADS_DEPLOYMENT_HISTORY" -ar "ADS_DEPLOYMENT_REVERSAL" -as "ADS_DEPLOYMENT_SUMMARY" \
  -wi "PROJ-123" -wd "path/to/deployment" \
  -re -td "path/to/templates"

The -td (template directory) flag is required for reversal because DataStar needs the component templates to generate the undo scripts.

Exporting a reversal script ahead of time

Reversal scripts are normally generated during a deployment and stored in the audit tables. You can also generate one ahead of a deployment, straight to a .sql file, without auditing configured and without running the deployment. This is useful when you want a rollback script to review, archive, or hand to an operator before you commit the change.

On the Deployment tab, use Export Reversal Script (on the toolbar, or right-click the grid). DataStar:

1. Prompts you for the target database to generate the reversal against.
2. Prompts for a file to save to (pre-filled with a name like reversal_PROJ-123_DEV-SQL_20260628153012.sql).
3. Reads the current state of each component from that database and writes an ordered compensation script - including DROP statements for components that don't yet exist in the target, so a deployment that adds objects can still be undone.

Output format

The format follows the target database, and for Oracle you choose in the save dialog:

• SQL Server - a single .sql file, batches separated by GO.
• Oracle - either a single .sql file (PL/SQL blocks terminated with /) or a .zip package. The package contains each reversal script, a deploy.sql master you can run in SQL*Plus or SQLcl (@deploy.sql, with SET DEFINE OFF already set), and a manifest.mf. The .zip runs each script separately - the most reliable way to apply an Oracle reversal - and is portable across Windows, macOS and Linux.

To apply the reversal later, run it in your database client, or load it through Deployment → Run External Script, which accepts both the .sql and the .zip.

Generate it before you deploy

The reversal captures the database as it is right now. Run Export Reversal Script before the deployment, while the target still holds the old state. Generate it afterwards and it simply captures the new state, which won't roll anything back.

Unlike the reversal generated during deployment, this artifact is not written to the audit tables and isn't tied to a deployment-history record - it's a standalone script you manage yourself, and it doesn't need the audit tables to exist.

If you only want the current object definitions - without the drop-for-new handling or rollback ordering - use Save DB Snapshot instead, which writes the last Database Snapshot to the same .sql/.zip format.

Viewing deployment history

Open Deployment → History. This tab shows every deployment recorded in the audit tables, newest first:

• Each row is a deployment: work item, version, date, and status.
• Expand a row to see the individual components that were deployed and the execution log.
• The reversal scripts stored for that deployment are available from this view.

The Deployment History tab only appears when the three audit tables exist and are configured in workspace settings.

Applying a reversal

From the client

From the Deployment History tab, select the deployment you want to reverse and use the Apply Reversal action. This extracts the stored reversal scripts and runs them against the current database connection.

You can also export the reversal scripts to disk and review them before running.

From a CI/CD pipeline

See the pipeline-specific guides:

• Azure DevOps reversal pipeline
• Octopus Deploy reversal

Inverse reversal mode

By default, reversal scripts run in the same order as the original deployment. Enable Use Inverse Reversal Mode in Workspace → Settings → General to run them in reverse order (bottom-up). Use this when your deployment process expects undo operations to run in the opposite sequence to the original apply.

The CLI equivalent is the -ir flag.

Custom script reversal

Data components are reversed automatically; DataStar generates the undo script from the database state captured before deployment. Custom scripts need metadata tags so DataStar knows how to reverse them.

Marking a script as custom

Add a metadata comment to the first line:

-- Metadata: {"custom":"true"}

The --SWITCH mechanism

Use --SWITCH comments to control which statements run during deployment versus reversal. A --SWITCH at the start of a line comments it out; during reversal, it moves to the end of the line (uncommenting the statement) and vice versa.

Original script (runs during deployment):

-- Metadata: {"custom":"true"}
--SWITCH INSERT INTO dbo.T1 (column_1, column_2) VALUES ('value1', 'value2');
INSERT INTO dbo.T1 (column_1, column_2) VALUES ('value3', 'value4'); --SWITCH

Generated reversal script (runs during rollback):

-- Metadata: {"custom":"true"}
INSERT INTO dbo.T1 (column_1, column_2) VALUES ('value1', 'value2'); --SWITCH
--SWITCH INSERT INTO dbo.T1 (column_1, column_2) VALUES ('value3', 'value4');

The first INSERT is commented out during deployment but active during reversal. The second INSERT is active during deployment but commented out during reversal.

Alternative: conditional logic

For more complex reversal logic, use a variable-based approach:

-- Metadata: {"custom":"true"}
DECLARE @ReversalMode BIT = 0
--SWITCH SET @ReversalMode = 1;

IF @ReversalMode = 0
BEGIN
    -- deployment logic
    INSERT INTO dbo.Config (KeyName, Value) VALUES ('Feature.X', 'enabled');
END
ELSE
BEGIN
    -- reversal logic
    DELETE FROM dbo.Config WHERE KeyName = 'Feature.X';
END

During deployment @ReversalMode is 0; during reversal the --SWITCH flips the SET statement, making it 1.

Static scripts

Scripts tagged as static are included in the reversal package unchanged: they are not reversed, just copied as-is. This is typically used for scripts that enable or disable constraints around a deployment:

-- Metadata: {"static":"true"}
ALTER TABLE BUSINESS_RULE NOCHECK CONSTRAINT ALL
ALTER TABLE BUSINESS_RULE_GROUP NOCHECK CONSTRAINT ALL

Static scripts run in the same position in the reversal as they did in the original deployment, so constraint-disabling scripts at the start of a deployment will also run at the start of the reversal.

What's next

• Deployment files: building and running a deployment.
• Deployment History tab: viewing past deployments.
• Azure DevOps reversal pipeline: automating reversal in CI/CD.
• DataStar.Tools CLI reference: all reversal-related flags.