Sometimes generating and using a baseline script is problematic for the following reasons.

1. It might take a long time to execute due to a large number of schema objects, or a large number of migration scripts.
2. It might be a legacy database that has invalid objects that can't be rebuilt from scratch.
3. It might rely on external dependencies, which are not available on a build system. 

In these scenarios, using a backup as the baseline is a good option. Only one person needs to configure this for your project.

Preparing your backup

1. Get a copy of your production database. 
   If you don't have access to Production, that's ok.  You just need a copy of what Production looks like.  This could be a recent backup, a cleansed Staging environment, or maybe even asking a Production DBA to generate a SQL Compare snapshot or Schema Compare for Oracle snapshot for you to use.  (If using a snapshot, you'll also need a copy of the data in the flyway_schema_history table.)
   
   

2. Keep only what you need in the backup.
   For the optimal performance, keep only the database objects that you need.  You'll also need to delete or clean all the transactional data, especially any sensitive data. The smaller the backup, the faster it will be to restore.

   SQL Server tip

   If deleting the transactional data from your database is a challenge, consider using DBCC CLONEDATABASE to create a schema-only copy of your database and use this as a source of your backup. Note that the flyway_schema_history, which is used by flyway to track which scripts have already been applied to the database, will need to be reinstated.

   -- Create a schema only copy your database and make it writeable
   DBCC CLONEDATABASE (<existing_database_name>, <new_database_name>)
   ALTER DATABASE [<new_database_name>] SET READ_WRITE WITH ROLLBACK IMMEDIATE
   
   -- Copy the flyway_schema_history table from the original database to the new empty database (the schema only copy)
   Use <new_database_name>
   
   INSERT INTO flyway_schema_history
   SELECT * 
   FROM <existing_database_name>.<schema_name>.flyway_schema_history
   

Make sure your backup file is accessible.

1. Add your backup to the repository
   This is recommended if you have a schema only or small backup.  You don't want a full transactional data backup in the repo, especially if the backup contains any sensitive data.
   This is nice because it is then accessible from all machines; all developers machines, CI/CD agents. 
   Check this BaselineBackup into your repository at the root of the Flyway Project. We recommend creating a new folder called “baseline”.  (This should appear alongside the schema-model and migrations folders in your project.)  You'll need to make sure the SQL Server restoring this backup has access to this location.
   
   
   

   You may need to start your local SQL Server service to Log On as Local System account to ensure it has permissions to access the backup file.

2. Have your backup on a fileshare, which is accessible from all machines and CI/CD agents.
   
3. Have your backup accessible from all the machines in the same location.  E.g., the backup resides on the D:\FlywayBackup folder on all SQL Server environments it's needed (e.g., if building from scratch on a CI system or using temporary databases for the flyway check command).

Set up your Baseline Script to restore the backup

Edit the baseline script

In your Flyway project's migrations folder, create or replace the baseline migration script.  This script will be named B_<number>_<yyyymmddhhmmss>__MyDatabaseBaseline.sql.  Note - there must be a double underscore between the numbering information and the filename description.

DECLARE @BackupFilePath NVARCHAR(128) = N'${flyway:workingDirectory}\migrations\<BACKUP_FILENAME>.bak'
DECLARE @DatabaseName NVARCHAR(128) = N'${flyway:database}'
DECLARE @mySQL NVARCHAR(MAX)

USE [master]
 
-- Close any connections to the shadow/build/check database 
SET @mySQL = CONCAT('ALTER DATABASE ', @DatabaseName, ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
EXEC sp_executesql @mySQL

-- Restore the database from the backup using the correct logical file names
RESTORE DATABASE [${flyway:database}]
FROM DISK = @BackupFilePath
WITH REPLACE
 
-- Put the shadow/build database back in multi_user mode
SET @mySQL = CONCAT('ALTER DATABASE ', @DatabaseName, ' SET MULTI_USER');
EXEC sp_executesql @mySQL

When an empty database needs to be migrated, it will use the baseline script, which will restore the database using the backup file and then run any pending migrations.