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. 

In these scenarios, provided each developer has a local instance of the database on which to restore a backup, 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.

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

   If deleting the transactional data from your database is a challenge, consider using DBCC CLONEDATABASE to create a schema-only copy of your database. Note that the flyway_schema_history will need to be reinstated.

   -- Restore a backup of your database and create a clone as follows:
   DBCC CLONEDATABASE (StackOverflow, StackOverflow_Clone);
   -- now we make it writeable
   ALTER DATABASE [StackOverflow_Clone] SET READ_WRITE WITH ROLLBACK IMMEDIATE
   

   The clone is a schema-only copy so the flyway_schema_history needs to be reinstated. Either use a tool such as SQL Data Compare to copy the data from flyway_schema_history to the clone or run flyway baseline to set the appropriate version as described in step 3 below. Once done we have a database from which a Baseline Backup can be created.

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 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.

Set up your Baseline Script to use Restore the Backup

Edit the baseline script

In your Flyway project's migrations folder, there should be a baseline migration script (the filename will be in the following format B5__my_database.sql), replace the contents of the baseline script with the following:

DECLARE @BackupFilePath NVARCHAR(128) = N'${flyway:workingDirectory}\migrations\PLACEHOLDER.bak'
 
DECLARE @mySQL NVARCHAR(MAX)
DECLARE @currentDatabaseName NVARCHAR(128) = N'${flyway:database}'

-- Set the database name and backup file path
DECLARE @DatabaseName NVARCHAR(128) = N'${flyway:database}'

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

-- Check if the database already exists, and if it does, drop it (optional)
-- TODO: Use the Database name variable
IF EXISTS (SELECT name FROM sys.databases WHERE name = @DatabaseName)
BEGIN
    ALTER DATABASE [${flyway:database}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [${flyway:database}];
END

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

-- create schema history TABLE
USE [${flyway:database}]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[${flyway:table}](
	[installed_rank] [int] NOT NULL,
	[version] [nvarchar](50) NULL,
	[description] [nvarchar](200) NULL,
	[type] [nvarchar](20) NOT NULL,
	[script] [nvarchar](1000) NOT NULL,
	[checksum] [int] NULL,
	[installed_by] [nvarchar](100) NOT NULL,
	[installed_on] [datetime] NOT NULL,
	[execution_time] [int] NOT NULL,
	[success] [bit] NOT NULL,
 CONSTRAINT [flyway_schema_history_pk] PRIMARY KEY CLUSTERED 
(
	[installed_rank] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[${flyway:table}] ADD  DEFAULT (getdate()) FOR [installed_on]
GO

-- If your backup does not contain a schema history table, you will need to uncomment this.
/*  
-- create schema history TABLE
USE [${flyway:database}]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[${flyway:table}](
	[installed_rank] [int] NOT NULL,
	[version] [nvarchar](50) NULL,
	[description] [nvarchar](200) NULL,
	[type] [nvarchar](20) NOT NULL,
	[script] [nvarchar](1000) NOT NULL,
	[checksum] [int] NULL,
	[installed_by] [nvarchar](100) NOT NULL,
	[installed_on] [datetime] NOT NULL,
	[execution_time] [int] NOT NULL,
	[success] [bit] NOT NULL,
 CONSTRAINT [flyway_schema_history_pk] PRIMARY KEY CLUSTERED 
(
	[installed_rank] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[${flyway:table}] ADD  DEFAULT (getdate()) FOR [installed_on]
GO
*/  

When your shadow database needs to be migrated, it will use the provided .BAK file and restore the database.