Introduction

A baseline is a representation of what has already been deployed to production at a point in time.  The current Flyway workflow is to create a SQL script called a baseline script from a production environment that contains all the CREATE DDL to rebuild all the objects in the database from scratch in a new environment.  The baseline script is used to provision new database environments, like development, shadow, or spinning up environments from scratch in a CI/CD pipeline. Baseline scripts for large databases can be millions of lines long and difficult to debug if something goes wrong (e.g., invalid objects or references to linked servers that aren't accessible). Using a backup file as a baseline is an alternative way to provision databases and avoids the baseline script issues. Restoring a schema-only backup is usually faster than running a large baseline script, so performance when generating migrations or spinning up new databases is also improved by using a database backup as the baseline. 

In this document, we will outline the steps to create a schema-only backup and use it to provision a shadow database.

Note: An enterprise license is required to use backups as baselines.

Getting a schema-only backup

Create a schema-only clone

The first step is to take a schema-only backup of the production database, which in this walkthrough will be called AdventureWorks2022. In order to achieve this, we must first create a schema-only copy of our database, which can be done using the DBCC CLONEDATABASE command:

DBCC CLONEDATABASE(AdventureWorks2022, AdventureWorks2022_Copy) with VERIFY_CLONEDB;

After running the statement above, we will have another database alongside AdventureWorks2022 called AdventureWorks2022_Copy. The new AdventureWorks2022_Copy database will have the same objects (e.g. schemas, tables, procedures, ...) as the AdventureWorks2022 database, but without any of the data. 

Optionally include data from the flyway_schema_history table

If your production database is already baselined, then you may wish to include a copy of the flyway_schema_history table data in the backup, as the backup will then retain the same version. That is, if your production database is at migration version V500, then restoring the backup to the shadow database will version it to V500 as well. If the flyway_schema_history table is not present in the backup then you will need to specify a backupVersion that the backup represents (covered later).

In order to copy the data from the flyway_schema_history table, first make the cloned database writable and then copy the records from the original flyway_schema_history to the copy in the cloned database:

ALTER DATABASE [AdventureWorks2022_Copy] SET READ_WRITE WITH ROLLBACK IMMEDIATE
INSERT INTO [AdventureWorks2022_Copy].dbo.flyway_schema_history SELECT * FROM [AdventureWorks2022].dbo.flyway_schema_history

Create the backup file

The schema-only backup can now be created using the AdventureWorks2022_Copy  database with the BACKUP command:

BACKUP DATABASE [AdventureWorks2022_Copy] TO DISK = N'AdventureWorks2022_Copy.bak';

An AdventureWorks2022_Copy.bak file will be created in SQL Server's default backup directory. You can optionally specify the full file path for the backup file, please see Backup Devices for the full list of options.

Now that we have the backup, the AdventureWorks2022_Copy database can be deleted as it's no longer needed. This can be done with the standard DROP DATABASE command:

DROP DATABASE [AdventureWorks2022_Copy];

Using the backup to provision a shadow database

The next step is to configure the shadow environment to use the backup file. By provision, we mean that Flyway will restore the backup to the shadow database when necessary.

One important aspect to consider is the location of the backup file. If the backup will be restored onto the same SQL Server instance from where it was taken, then the backup file doesn't need to be moved, as it will be accessible to the SQL Server process. If the shadow environment will be on a different SQL Server instance, then we must ensure the backup file is accessible to that instance. For example, it may be necessary to:

• Copy the backup file to the machine running the SQL Server instance which will host the Shadow database.
• Expose the backup file on a network share accessible to the machine running the SQL Server instance which will host the Shadow database.

In this example, the backup file will be restored onto the same SQL Server instance so don't need to move it. 

[environments.shadow]
url = "jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks2022_Shadow;trustServerCertificate=true"
user = "sa"
password = "..."
provisioner = "backup"

[environments.shadow.resolvers.backup]
backupFilePath = "AdventureWorks2022_Copy.bak"
backupVersion = "001"
sqlserver.generateWithMove = true

$ flyway info -environment=shadow
...
Creating Schema History table [AdventureWorks2022_Shadow].[dbo].[flyway_schema_history] with baseline ...
Successfully baselined schema with version: 001
...
Schema version: 001

+----------+---------+----------------------+----------+---------------------+----------+----------+
| Category | Version | Description          | Type     | Installed On        | State    | Undoable |
+----------+---------+----------------------+----------+---------------------+----------+----------+
|          | 001     | Baseline from backup | BASELINE | 2024-12-19 16:00:55 | Baseline | No       |
+----------+---------+----------------------+----------+---------------------+----------+----------+

Note

1. The backup file must be accessible to the SQL Server that it is being restored to.  You may need to:
   1. Copy the backup file to the machine running the SQL Server instance which will host the shadow database.
   2. Expose the backup file on a network share accessible to the machine running the SQL Server instance which will host the shadow database.
2. The shadow user will need the necessary permissions to restore the database backup.
3. DBCC CLONEDATABASE has some limitations around System-Versioned Temporal Tables, Ledger Tables, and some system catalogs related to service broker or other system-level objects.  If you need these in your provisioned database after the backup is restored, then script them in an afterBaseline callback.