Below are some example changes that may require an extra review if the migration script is generated from the state of the database:

Working with transactional data

If you need to insert/update/delete transactional data, then a schema comparison won't work for this.  You will need to write your own migration script to manipulate the transactional data.

Adding/changing indexes

If you need to add or change an index, this might impact the amount of time the deployment takes.  Especially for a table with many records.  Get a DBA  involved to determine the best way to make this change.

Anything that needs to be done outside of a transaction

If a changes is needed that can not be wrapped in a transaction, then take extra care because Flyway will not be able to rollback these changes.  If your deploying 3 pending scripts (1st is transactional, 2nd is not transactional, 3rd is transactional), then you may be left in a state were only part of your deployment is applied (e.g., script 1 in this case).

Adding a NOT NULL constraint to a column

If you add a NOT NULL constraint to a column in a table that contains NULL entries, without a default value, the deployment will fail.

Rather than adding a default value, you can write a migration script to update all the existing NULL entries with a NOT NULL value before adding the NOT NULL constraint to the column.

Any bespoke scripts if deploying to different clients

If you need bespoke schema changes or data sets for different clients, make sure the scripts are in the right folder location, or the scripts have the proper guard clauses or configuration files to ensure that they are running on just the environment/client you need it to run on.

Splitting a column

From a schema point of view, when you split a column you actually create two new columns and drop the original one. If you deploy this change, any data in the dropped column will be lost.

To avoid this, you can write a migration script to copy the data to the new columns before dropping the original column. For a walkthrough of this process, see Splitting a column without data loss.

Merging columns

From a schema point of view, when you merge a column, you create a new column and drop the original columns. If you deploy this change, any data in the dropped columns will be lost. 

To avoid this, you can write a migration script to copy the data from the original columns to the new column before dropping the original columns.

Splitting or merging tables

You can follow the same steps for splitting or merging a tables as you would for splitting or merging columns. To avoid data loss, break the change into multiple commits, and write a migration script to copy the data:

Splitting a table

1. Create the new table.    
2. Migration script to copy the data to the new table.    
3. Drop the columns in the original table.

Merging a table

1. Create the new table.    
2. Migration script to copy data from the old tables to the new table.    
3. Drop the original tables.

Changing the data type or size of a column

When you change a column's data type, data might be lost. For example, if the data type you change it to doesn't accommodate some of the rows, data will be truncated during deployment. 

To avoid this, you can create a migration script to appropriately modify rows that would otherwise be truncated.

In Flyway Desktop, you will be given a warning if a generated script may cause data loss (e.g., going from NVARCHAR(10) to NVARCHAR(5)).  You might want to write an IF statement to check the MAX(SIZE) of the data in that column before truncating.

Renaming a table

When you rename a table in Management Studio, the Redgate comparison technology may interpret this as dropping the table with the old name and creating a new table with the new name. If the table contains data, the data will be lost. 

To avoid this, you can create/edit a migration script to rename the table with the sp_rename stored procedure in SQL Server or other syntax for your database.

Filegroups and/or partitions 

Filegroups and/or partitions may differ across your environments (e.g., Development > Test > UAT > Staging > Prod).  Therefore, these scripts may need review and customization.

Undo scripts 

The generated undo scripts are just a starting point to get back to the previous version.  You may want to undo the change in a different way.  E.g., if you added a column, maybe you just want to rename the column so any data in that was entered in the meantime is maintained.

Let us know if there's anything missing from this list that you regularly check.