A side effect of migrations is that you can end up with lots of scripts to run to get to your current state.

To keep your project as maintainable as possible, we suggest utilizing programmable objects or grouping migrations into folders. 

Using programmable objects allows SQL Change Automation to create reusable object deployment scripts. For most environments, this should reduce the number of migrations in your project by a significant amount.

Grouping migrations into folders allows you to keep your project easier to navigate. The typical granularity for these folders is either per-release or per-feature-branch.

If you have already tried using programmable objects or grouping migrations and you are still left with an unmanageable number of migrations, you may need to consider reorganizing or re-baselining your project. We recommend you reorganize your project by merging/concatenating migration files. Alternatively, you can re-baseline your project by compressing your migrations.

Reorganization method

This method involves concatenating a series of migration scripts into a single file. The advantage of this approach is that you can choose to either consolidate all migrations or just a subset of migrations within your project. Unlike the re-baselining method, this method is less risky because it does not involve re-calculating your migrations.

Re-baselining method

This approach involves re-importing your database,  resulting in a single migration script being generated (starting again from 0001.sql) containing all of the logic needed to deploy the database from scratch.

Before proceeding to re-baseline your project, it is essential to confirm that all of your target databases (including all development, test, production and external customer environments) have upgraded to the version of the database in your master branch, and do not contain new changes from any feature branches. If any databases are pending migrations, it is recommended that these are deployed to bring your environments in-sync with your project, before proceeding.

Currently, the simplest way to rebaseline is to create a new project with a fresh baseline off the development database.

Suggested steps for implementing the reorganization method

Firstly, you'll need to pick a high-watermark up to which to merge your migrations. This point should be no higher than the most out-of-date database in all of your internal and external environments. For example, if your Development environment has had migrations 1-to-10 applied, but Production has only had 1-to-6, then the high-watermark will be the file named as 0006_[suffix].sql. 

Once it has been confirmed that all of your databases are up-to-date, the process for merging your migration files is:

1. Branch your workspace from master
2. Locate and open the high-watermark migration (see above) and copy the value of the ID property from the header (found within the <Migration/> tag)
3. Create a new folder in the root of the project to contain the files to merge
4. Move migrations to be consolidated (that is 0001.sql through to the high-watermark migration) to the merge folder
5. Use a file concatenator utility to merge the migrations together. You can do this by opening a command prompt window, navigating to the merge folder, and executing the following command:
   copy /b *.sql 0001_Consolidated_Migrations.sql
6. Move the consolidated migration file to the wanted migrations folder, and add it to your project
7. Open the consolidated file in SSMS or Visual Studio. Remove all migration metadata entries, keeping only the first one and replacing the rest with spaces. To locate the metadata entries, use the following regex: [\p{C}]-- <Migration ID="[a-zA-Z0-9\-]{36}" (.*?)*/>(?:[\r\n]+GO)?
8. Using the ID copied in step 2, paste over the existing value into the ID property of the remaining <Migration /> tag at the top of the file
9. You can now refresh the list of differences or verify the project