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 Create and Configure a liquibase.properties File. 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: For best results, specify all commands and parameters in the --kebab-case format in the CLI. If your preference is camelCase, it also works in the CLI.

Attribute Definition Requirement
--changelog-file *

The root changelog

 Required
--url

The JDBC database connection URL

 Required
--username

The database username

 Required
--password

The database password

 Required
--output-file **

Specifies the file path to where the SQL output 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.