SQL Change Automation 4

Switching branches

There are three ways to switch branches:

  • Use SQL Clone to provision a database per branch
  • Drop and recreate your database in the destination branch
    This is the simplest method. The only disadvantage is that you'll lose transactional data. If you need this data for testing, you'll have to repopulate your databases.
  • Resync your schema to match the destination branch
    Use this method if you need to preserve the contents of your database, and if there are only small differences between the branches.

SQL Clone

SQL Clone can be used to create full copies of your database for each piece of work during development, and switch between them automatically when you change branch in git. This allows you to make changes to your database on one branch, and store those changes when you need to work on another branch. When you return to the original branch all your database changes including data will be preserved.

See the SQL Clone documentation for more details.

Drop and recreate your database

The simplest way to switch branches is to drop and recreate your database. This resets your environment so you can deploy from the new branch. To do this:

  1. Close the solution or projects within your IDE of choice.

  2. Take a backup of the database contained in your projects.
  3. From your local SQL Server instance, drop the database.
  4. In version control, select the branch you want to switch to.
  5. Reopen the solution or projects.
  6. Apply the project scripts to your empty database.

You can also follow these steps to test whether your databases can be reproduced from source files.

Revert pending changes to re-sync your schema 

If you need to preserve the contents of your database, and there are only small differences between branches, re-sync the schema to match the destination branch before you deploy:

  1. In version control, select the branch you want to switch to.
  2. Open the solution or projects within your IDE of choice.
  3. View the list of object differences between your development database and the applied migrations.
  4. Select all the objects in the list, then perform revert or undo
    The pending object list refreshes and shows objects as identical.
  5. Apply any unapplied project scripts.

If there are multiple databases in your solution, you'll need to repeat steps 4 and 5 for each database.

What differences can cause the resync to fail?

Resyncing your schema will work as long as there aren't substantial differences between the two branches. For example, if branch A contains a migration that dropped a not-null column, clicking Revert selected objects on branch B  can fail. SQL Server can't recreate the column containing the data.

To prevent this problem in future, you could start taking regular backups of your databases. Then you can restore your development environment to a previous known state before deploying your solution.



Didn't find what you were looking for?