Backup Provisioner
Published 15 January 2025
- Status: Preview
This provisioner allows for the provisioning and re-provisioning of databases using a database backup file.
Benefits of using the backup provisioner:
- The database backup can contain static data and a
flyway_schema_history
table, allowing an environment to be provisioned with data to a desired version. - Restoring a database backup is not impacted by references to invalid objects. This makes the backup provisioner a good alternative to a baseline script.
- Speeding up shadow provisioning - restoring a backup file that represents version 1000 is considerably quicker than running 1000 migrations scripts.
Supported backup file formats
The following database engines and backup file formats are supported:
- SQL Server backup files (
.bak
). - Oracle dump files (
.dmp
) generated using the Data Pump Export toolexpdp
.
Prerequisites
- A database backup file, which could be a backup of production database for example. This backup file needs to be in a location accessible to the database server that will be provisioned i.e. on the database server itself or on a network share.
For SQL Server:
- The environment URL must have the
databaseName
parameter set to the name of the database that the backup will be restored to. If this database doesn't yet exist on the target server then it will be created by the backup provisioner.
For Oracle:
- The schemas that will be restored from the dump file must already exist on the target database.
- The Oracle Data Pump Import tool
impdp
must be installed and available on thePATH
of the machine running Flyway. - The user specified for the environment must have the
IMP_FULL_DATABASE
privilege, and aREAD
privilege on the directory where the dump file is located.
To configure this provisioner:
- Set the value of the provisioner parameter to
backup
. - Populate the following resolver properties in the TOML configuration or as command line arguments:
backupFilePath
- (Required) The file path of the backup file. Note: this needs to be accessible/relative to the database server that is being provisioned.backupVersion
- (Optional) The migration version the backup file represents. This property is required when the backup file doesn't contain aflyway_schema_history
table. In this scenario aflyway_schema_history
table will be created once the backup has been restored and a baseline entry with versionbackupVersion
will be inserted into theflyway_schema_history
table. If the backup file does contain aflyway_schema_history
table then this property is optional. If left unset then theflyway_schema_history
table from the backup will be restored unaltered, otherwise theflyway_schema_history
will be updated to baseline versionbackupVersion
.
Below we consider configuration and examples for each supported database engine.
SQL Server
Example Configuration
The backup provisioner can be configured in the TOML file as follows:
[environments.shadow]
url = "jdbc:sqlserver://localhost:1433;databaseName=MyDatabase;trustServerCertificate=true"
user = "MyUser"
password = "${localSecret.MyPasswordKey}"
provisioner = "backup"
[environments.shadow.resolvers.backup]
backupFilePath = '\\DBOps1\Backups\backup.bak'
backupVersion = "995"
This example will restore the backup file located at \\DBOps1\Backups\backup.bak
to the MyDatabase
database in the shadow environment. The MyDatabase
database will be at version 995. This example represents a common scenario where a user may wish to reset the shadow environment to the same state as production, so that new development migrations can then be applied to the shadow and verified.
SQL Server WITH MOVE
The T-SQL "WITH MOVE" syntax makes it possible to specify the file paths on disk that data and log files should be restored to. This can be required in a couple of scenarios:
- The directory structure on the target database doesn't match that of the source database. For example, the source
database stores data and log files under the
C:
drive, whilst the target database has noC:
drive and stores database files under theD:
drive. - The data and log file paths embedded in the backup file are already being used by a different database. For example, taking a database backup and restoring it on the same SQL Server instance but under a different database name, so that the restored database exists alongside the original. In this scenario, new file paths to restore the data and log files must be provided when performing the restore.
The backup provisioner provides two methods for adjusting the data and log files paths when restoring a database.
Auto-generate data and log file paths
The backup provisioner exposes a generateWithMove
boolean parameter, which defaults to false. When set to true, the
backup provisioner will auto-generate file paths for any data and log files contained within the backup file, and
restore the backup using these generated file paths. The example TOML below shows how this can be enabled:
[environments.shadow.resolvers.backup]
backupFilePath = '/tmp/backup/backup.bak'
backupVersion = "995"
sqlserver.generateWithMove = true
Specify data and log file paths
An alternative to the generateWithMove
parameter above is to specify the exact file path that data and log files
should be restored to. The example TOML below shows how this can be done:
[environments.shadow.resolvers.backup]
backupFilePath = '/tmp/backup/backup.bak'
backupVersion = "995"
[[environments.shadow.resolvers.backup.sqlserver.files]]
logicalName = "NewWorldDB"
filePath = "/var/opt/mssql/data/NewWorldDB_shadow_data.mdf"
[[environments.shadow.resolvers.backup.sqlserver.files]]
logicalName = "NewWorldDB_log"
filePath = "/var/opt/mssql/data/NewWorldDB_shadow_log.ldf"
Where:
logicalName
is the logical name of a data or log file in the backup file.filePath
is the file path on disk where the logical file will be restored to.
Note: When file paths are provided as above, then the generateWithMove
parameter is ignored.
i.e. User defined file paths take precedence over auto-generated file paths.
Oracle
Additional parameters
The backup provisioner supports the following additional parameters for Oracle databases:
connectionIdentifier
(Required) - The connect identifier used to connect to the target database i.e. An Oracle*Net connect descriptor or a net service name (usually defined in thetnsnames.ora
file). TheconnectionIdentifier
must contain or map to all data required to log in to the database without any user interaction. That is, using theconnectionIdentifier
should not require a username or password to be entered.importContent
(Optional) - The content to restore to the target schema. Valid values are:METADATA_ONLY
(Default) - Loads only database object definitions i.e. no data.ALL
- Loads database object definitions and data.
schemaMapping
(Optional) - A map specifying the new schema names for the schemas in the dump file. The key is the schema name in the dump file and the value is the new schema name that it should be mapped to. This is useful when we wish to restore a schema from the dump file to a different schema name on the target database.
Example Configuration
The backup provisioner can be configured in the TOML configuration as follows:
[environments.shadow]
url = "jdbc:oracle:thin:@//localhost:1521/XE"
user = "DEV"
password = "${localSecret.MyPasswordKey}"
schemas = ["SHADOW"]
provisioner = "backup"
[environments.shadow.resolvers.backup]
backupFilePath = "DATA_PUMP_DIR:dev.dmp"
backupVersion = "995"
oracle.connectionIdentifier = "/@MYALIAS"
oracle.importContent = "METADATA_ONLY"
[environments.shadow.resolvers.backup.oracle.schemaMapping]
"DEV" = "SHADOW"
This example will restore the backup file located at DATA_PUMP_DIR:dev.dmp
to the SHADOW
schema in the shadow
environment. The SHADOW
schema will be at version 995.
Some additional points to note are:
- A backup of the
DEV
schema has been taken and exists at the locationDATA_PUMP_DIR:dev.dmp
. The dump file location is given in the formatDIRECTORY:filename
whereDATA_PUMP_DIR
is a default Data Pump directory available to use, anddev.dmp
is the dump file name. - The
DEV
schema in the dump file will be restored to theSHADOW
schema in the target shadow environment. MYALIAS
is atnsnames.ora
connection alias has been defined in thetnsnames.ora
and an Oracle wallet with a password forMYALIAS
has been created and configured in thesqlnet.ora
.
If you do not wish to set up a tnsnames.ora
connection alias or Oracle wallet, then a connection string of the form
username/password@[//]host[:port][/service_name]
can be used instead for the connectionIdentifier
. For example, a
DEV
user with the password DEV_PASSWORD
connecting to a database on localhost
with the service name XE
on port
1521
could have the connectionIdentifier
specified as follows:
oracle.connectionIdentifier = "DEV/DEV_PASSWORD@localhost:1521/XE"