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.

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.