ROLLBACK DATABASE

Roll back a database to a backup snapshot.

ROLLBACK DATABASE TO SNAPSHOT 'rollback_point' HOT_STANDBY

DATABASE: This command always rolls back the current database. Before running this command, you must connect to the database that you want to roll back. You cannot roll back the yellowbrick database.
SNAPSHOT 'rollback_point': The snapshot you specify should be the result of the sys.oldest_rollback_point_id_in_replica function.
HOT_STANDBY: The database that you roll back is placed in HOT_STANDBY mode. If necessary, you can use an ALTER DATABASE command to take the database out of HOT_STANDBY mode.

Use Cases

There are two main use cases for a rollback operation:

The most common use case for rolling back a database is as part of a failover procedure when database replication is in use. A rollback of this kind occurs automatically as part of an ALTER DATABASE...ALTER REPLICA...PROMOTE command. You do not need to run the ROLLBACK DATABASE command manually in this case. See Replication Failover and Failback.
You can roll back a target database that is being used for replication manually if you have taken a target database out of HOT_STANDBY mode for some reason, such as to run some tests that require writes. After you have run these tests, the target database and source database will be out of sync, and you will not be able to resume replication. Resuming replication in this case requires you to roll back the database to a known good snapshot (a backup snapshot created by the replication process). Rolling back a database also puts the database back into HOT_STANDBY mode, which is required before replication can resume. See also Rolling Back a Replicated Database.

The following command rolls back a target database for replication:

premdb_replicated=# rollback database to snapshot 'premdb_replica_20_02_10_17_56_08' hot_standby;
ROLLBACK DATABASE TO SNAPSHOT

Parent topic:SQL Commands