rollback-to-date-sql

The rollback-to-date-sql command is a helper command that allows you to inspect the SQL Liquibase will run while using the rollback-to-date command.

Uses

The rollback-to-date-sql command is typically used when you want to inspect the raw SQL before running the rollback-to-date command, so you can correct any issues that may arise before running the command.

Running the rollback-to-date-sql command

Before running the rollback-to-date-sql command, you can see the dates of all previous changes by running the history command.

Note: Unlike the rollback-to-date command, there is no impact to your database while using this command.

To run the rollback-to-date-sql command, specify the driver, classpath, and URL in the Liquibase properties file. For more information, see Specifying Properties in a Connection Profile. You can also specify these properties in your command line.

Then run the rollback-to-date-sql command:

liquibase --changelog-file=changelog.xml rollback-to-date-sql 2020-05-07

Note: Enter the name of the changelog and date you want to use in place of changelog.xml and 2020-05-07.

rollback-to-date-sql global attributes

Tip: All commands and parameters use the --kebab-case format in the CLI environment. This is the format Liquibase recommends for best results. If your preference is camelCase, it will still work in the CLI.

Attribute Definition Requirement
--changelog-file * Specifies the root changelog Required
--url Specifies the JDBC database connection URL Required
--username Specifies the database username Required
--password Specifies the database password Required
--output-file ** Specifies the file path to where the Rollback SQL will be written Optional

* Liquibase checks the changelog and any nested changelogs for the definitions of the changesets to roll back.

** If not specified, rollback-to-date SQL output goes to STDOUT.

Note: The username and password attributes are not required for connections and systems which use alternate means of authentication.

rollback-to-date command attributes

Attribute Definition Requirement
--date/time The date and time your database rolls back to. The date format is YYYY-MM-DD HH:MM:SS or YYYY-MM-DD'T'HH:MM:SS, however, it is possible to indicate the date or time only. Required 
Liquibase Community 4.9.1 by Liquibase
-- *********************************************************************
-- Rollback to 2022-05-04 Script
-- *********************************************************************
-- Change Log: example-changelog.sql
-- Ran at: 5/6/22, 1:16 PM
-- Against: DBUSER@jdbc:h2:tcp://localhost:9090/mem:dev
-- Liquibase version: 4.9.1
-- *********************************************************************

-- Lock Database
UPDATE PUBLIC.DATABASECHANGELOGLOCK SET LOCKED = TRUE, LOCKEDBY = 'DESKTOP-SQVSSV2 (192.168.1.179)', LOCKGRANTED = NOW() WHERE ID = 1 AND LOCKED = FALSE;

-- Rolling Back ChangeSet: example-changelog.sql::3::other.dev
ALTER TABLE person DROP COLUMN country;

DELETE FROM PUBLIC.DATABASECHANGELOG WHERE ID = '3' AND AUTHOR = 'other.dev' AND FILENAME = 'example-changelog.sql';

-- Rolling Back ChangeSet: example-changelog.sql::2::your.name
DROP TABLE company;

DELETE FROM PUBLIC.DATABASECHANGELOG WHERE ID = '2' AND AUTHOR = 'your.name' AND FILENAME = 'example-changelog.sql';

-- Rolling Back ChangeSet: example-changelog.sql::1::your.name
DROP TABLE person;

DELETE FROM PUBLIC.DATABASECHANGELOG WHERE ID = '1' AND AUTHOR = 'your.name' AND FILENAME = 'example-changelog.sql';

-- Release Database Lock
UPDATE PUBLIC.DATABASECHANGELOGLOCK SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

Liquibase command 'rollback-to-date-sql' was executed successfully.