This method involves using  SQL Change Automation  to generate a script at development time with the revert command (currently only available in Visual Studio), allowing the script to be set aside as a contingency in the event that a rollback is subsequently required. This approach can require more effort during the development cycle, however it ensures that for every up migration that changes the state of your database, there is also a script that can be used to return the database back to the previous state. Additionally, it also means that any nuances about how the database was changed can be captured in the down script at the same time that the up script is written.

Creating the down script

These steps generate both the script to go up (that is a migration script) plus the script to go down, as part of the connected change-authoring workflow:

1. Add a folder to the root of the project that will contain the  down  scripts in the project root (for example,  /Rollback)
2. Make a change in the connected database
3. Within the SQL Change Automation tool-window, refresh the list of differences between the development database and the project
4. Select one or more objects in the list, Right-click and  View Revert Script. When the popup appears, copy the displayed script to the clipboard

5. Import the changes to generate the migration (ensuring that the selections are identical to those used for the revert script)

6. Rename the migration to describe the change being performed (for example, /Migrations/002_Drop_Customer_Column.sql )
7. Using the Solution Explorer, add a new script within the  /Rollback  folder with a similar name to the migration (for example, /Rollback/002_Drop_Customer_Column_DOWN.sql )
8. Paste the script contents from the clipboard
   

If you find that the script doesn't roll back the change in the expected way, make any changes to the script as required prior to committing both scripts to version control

Testing the down script

To test that the down script works as expected, simply execute it within the document editor. After checking the schema/data to see if the change was rolled-back as expected, execute the corresponding up script (migration) to return to the current project state. Or alternatively, refresh the  SQL Change Automation tool-window, Right-click the pending changes and revert the objects to return to the current project state.

Alternatively, if you'd prefer to test the script without affecting your target database, you can test your down script by validating it against the  shadow database  instead:

1. Within the IDE, copy the down script to the same migrations folder as the up script
2. ( Optional step ) Copy the contents of the up script, paste it onto the end of the down script and save (This optional step includes the up script as part of the test in order to make it easier to confirm that the database state is being modified as intended (more about this in step 5))
   
3. Mark this script as applied
4. Verify the project for any errors
5. In the event of an error, adjustment the script as needed, mark as applied, and verify again
6. Delete the script (added in step 1)

Performing a rollback using down scripts

In the event that you need roll back changes that were introduced by your project's up scripts, we recommend simply moving one or more of the down scripts into an appropriate migrations folder so that the down scripts can be run at the end of your existing deployment pipeline.

Preparing to roll back begins by editing your SQL Change Automation project within Visual Studio:

1. In your version control client, update your workspace from the branch that was deployed to your target environment (typically "master")

   The next few steps are necessary in order to ensure that the Programmable-Object  folder structure is also included in the rollback. If you are not using the  programmable object  feature, or if you do not wish to rollback stored procedures, functions or views, skip to step 6

2. Locate all of the up scripts that were deployed in the release and copy them to a folder outside your version control workspace

3. Locate the corresponding down scripts that you wish to rollback, and copy them to a folder outside your version control workspace

4. In your version control client, update your workspace from a tag or branch corresponding to the pre-deployment state of your database (this can involve reverting commits from the master branch)

5. Within the IDE, restore the up scripts to the Migrations folder

6. Within the IDE, add the down scripts to the same folder as the up scripts
   If multiple down scripts are required to perform the rollback, then it can be necessary to reverse the order of deployment of the scripts in order to account for any dependencies between them
7. Test the down scripts against your local database by deploying the project/solution
8. Verify the project and update the offline schema model
9. Commit the project modifications to version control, and merge to the appropriate baseline in version control (for example, master)

Once the project has been built, you can then deploy the database in the normal way (for example, using Azure DevOps, Octopus Deploy, PowerShell, etc) to perform the rollback.