Version control for databases

The situation

You have a number of developers working on updates to your database schema. You need to prevent two developers making simultaneous changes to the SQL objects during the development process, so you use a source control program.

There may also be a need to restrict access to only certain schema objects in source control and as a further security measure, to compile a change history for the sake of auditing that details who did what and when.

When a database developer needs to revert to an earlier version of a SQL object, such as a view or stored procedure, the entire database must be considered, to account for dependencies. Unfortunately, using a source control product, the developer would see only the line-by-line differences in the SQL script for the particular object. There would be no cross-referencing to dependencies.

Reverting to an earlier version of the object by relying on only the line-by-line differences in the SQL script could render the entire database ineffective.

Key challenges

  • Keeping records of different versions of databases throughout the development process without storing a copy of the entire database each time.
  • Maintaining the integrity of the database when a developer needs to revert to a previous version.

How we can solve it

  1. Use SQL Compare Pro to compare your live Test database with the latest version of a SQL scripts folder under source control.
  2. When you need to revert to a previous version of an object, use SQL Compare® to select the object(s) in question and synchronize directly from the original SQL script under source control to the live test database.
  3. Use the SQL Changeset add-in to speed up checkin/edit/checkout and to ensure that you are comparing with the latest version of the database.

Benefits of our solution

  • Comparing and synchronizing directly to and from SQL scripts saves time.
  • The SQL Changeset add-in speeds up the process of working with scripts under source control.
  • A complete history of database development and changes is recorded and can provide a valuable audit trail.

Online help

  • For further information about SQL Changeset and working with scripts under source control, take a look at our worked example: Using SQL Changeset
  • An alternative method using snapshots: The configuration options in your source control provider may allow you to set your own options for a difference and merge tool. Using SQL Toolkit, you can create a difference tool that uses the features from SQL Compare to report the differences between two snapshots, and a merge tool to generate the migration SQL to upgrade or revert. To learn more about working with snapshots, take a look at our worked example: Working with snapshots