A side effect of migration-based database development is that over time you can end up with an unwieldy number of scripts to run to get to your current state.

To keep your project as maintainable as possible, we recommend utilizing the following SQL Change Automation project options:

• Enable programmable objects within your SQL Change Automation project. This provides a state-like deployment experience for stored procedures, views, functions etc. Basically for any type of object that can be dropped and recreated without loss of state, SQL Change Automation can create reusable object deployment scripts. For most environments, this should reduce the number of migrations in your project by a significant amount.
  

• Group migrations into folders and create "branch" folders with each release. This allows you to keep your migrations within version folders to make your solution easier to navigate. The typical granularity for these folders is either per-release or per- feature branch.

If you have already implemented the above options, and are still left with an unmanageable amount of migrations, you may need to consider reorganizing or rebaselining your project

Reducing the number of migrations in your project

There are two main approaches to reducing the amount of scripts within your SQL Change Automation project.

Option A: Consolidate your project through merging/concatenation of migration files (recommended)

This approach 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 or just a sub-set of migrations within your project; useful if your project contains migrations that have not yet been deployed to all of your target environments. Additionally, this approach is considered lower-risk than the re-baseline approach (described further below) as it does not involve re-calculating your migrations, which can result in loss of deployment logic, particularly if custom scripting has been included.

 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. Within Visual Studio, 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 (i.e. 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 Migrations folder, and add it to your project
7. Edit the file and press CTRL+H to open the Find & Replace window, and switch on Use regular expressions
8. Find the following string (without quotes):
   "\r\n(.*?)-\- \<Migration (.*?)\>\r\n"
   and replace it with this string (without quotes):
   "\r\nGO\r\n"
   This is to ensure that only a single set of migration metadata remains within your script.
9. Scroll to the top of the file. You should find a <Migration /> tag.
10. Using the ID copied in step 2, paste over the existing value in the ID property
11. Within the SQL Change Automation tool-window, click Refresh to verify your newly-consolidated scripts against the Shadow database

Option B: Re-baseline your project by "compressing" migrations

This approach involves effectively re-importing your database into your existing SQL Change Automation project, 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.

Once it has been confirmed that all of your databases are up-to-date, the process for re-baselining your project is:

1. Branch your workspace from master
2. Temporarily disable baseline-setting: right-click the project in the Solution Explorer, select Properties, and within the project designer, un-check Mark first folder as baseline (under Baseline)
3. Within Visual Studio, deploy your SQL Change Automation project to a "clean" instance of SQL Server (i.e. let SQL Change Automation deploy your database from scratch)
4. Locate and open the "latest" migration in your project and copy the value of the ID property from the header (found within the <Migration/> tag)
5. Archive (remove) all files from the Migrations folder. 
6. Temporarily move all files from the Programmable Objects folder to a different folder (ignore this step if you have not enabled this feature in your project)
7. Import from your clean database using the SQL Change Automation tool-window to generate the 001.sql file
8. Open the 001.sql file, and using the ID copied in step 2, paste over the existing value in the ID property
9. Move any Programmable Object files back into the Programmable Objects folder
10. If baseline-setting was disabled in step 2, re-enable it: right-click the project in the Solution Explorer, select Properties, and within the project designer, check Mark first folder as baseline (under Baseline)
11. Within the tool-window, click Refresh to verify your newly-consolidated project against the Shadow database