How To Get Your Database Under Version Control
Version controlling your database schema is important to ensure smooth development, deployment and
maintenance of a database. This article describes how to use the advantages of version control in
the process of database development.
A database schema can be represented as a set of database creation scripts. These scripts can be used
to create the database, including all tables, views, stored procedures and other objects from the scratch,
or update an existing production database to the newest version without causing loss of data. Naturally,
the best way to keep a database schema under version control is by keeping its creation scripts in the
source control system.
Database creation scripts are stored individually. A separate file for every object will contain a creation
script for that object. For example, a file named dbo.MyTable1.sql will contain a script for creating the
table MyTable1. For convenience, all scripts are grouped in several subfolders by the type of an object
they create, such as Tables, Views, Stored Procedures, and so on.
As the development goes, objects get updated, replaced, or removed from the database schema. When an object
has changed, you need to save a new creation script for that object in the source control system. As a result,
you will have a comprehensive history of changes to all objects in your database saved in the source control
system. This way you’ll be able to go back to any version of that database schema. You can specify versions
of database schemes by the date or assign meaningful labels. You can also annotate any changes being made
using check-in comments.
In order to get your database under version control, you’ll need to produce database creation scripts for
every table, stored procedure, and other objects in the database. You will then need to add the creation scripts
into your favorite version control system. While there can be many different ways to save the database as a
set of creation scripts and check it into the version control system, using SQL Examiner is the simplest way.
To save the initial creation scripts and add them into the version control system, Launch SQL Examiner and
choose Script Database Wizard from the Tools menu. The Wizard will ask you to specify the database you want
to add to the version control system, select database objects to add (you can check all objects), and specify
connection parameters for your version control repository.
The wizard works quickly, but may still take some time depending on the number of objects you have in your
database. After the wizard is finished, your database schema has been successfully saved into the version
Your initial version of the database is now safely stored in the repository. As you continue working with the
project, tables are added, changed and removed, stored procedures evolve, and objects are added to the database.
In order to keep your database schema under version control, you need to save all the changes into the version
In order to store the changes, commit the application code as you always do. SQL Examiner will help you commit
database scripts. Launch SQL Examiner and compare your current development database with the last version of the
database schema stored in the repository. After that, launch the Update Script Wizard, select the database,
specify objects you are updating, and follow to add the updated creation scripts into the repository. The new
database structure complete with all the changes will be saved as a new version in the source control system.
One of the benefits of using a version control system is the ability to investigate what changes have been made
from one version of a database to another. The two versions of a database don’t have to be two adjacent releases;
you can pick any two versions from the repository, different repositories or other sources.
In order to examine the differences between database versions, launch SQL Examiner and click the “New…” button on
the toolbar. You will be asked to specify the server, connection parameters, and the database name for both versions.
Click “Compare” to begin the comparison.
You can compare two database schemas from a variety of sources. Either source can be:
- A database running on the server;
- A version of a database in the version control repository;
- A set of database creation scripts on the hard disk or network location.
The comparison process is fast and simple. SQL Examiner makes it easy to quickly compare different versions of a
database schema coming from diverse sources.
One of the most important things a version control system is used for is the ability to update the production database
on the client side without losing data. It is quite common when a client has an older version of a database that must
be updated to the latest release, which has substantial differences and may require a different database structure to
operate. This situation means you’ll have to convert the database structure from one version to another. In a complex
database, a manual update would not only require a significant amount of labor, but would possess significant risks to
the integrity of the database and any data it stores.
Launch SQL Examiner allows automating the process, safely migrating the database schema from one version into another.
In order to safely migrate a database, you will need to have both versions of the database schema stored under the
version control system. First, follow Step 3 from this guide to create a new version comparison, specifying the sources
in the version control repository and selecting the two versions of a database, the production one and the one you are
updating to. In the same wizard, you’ll be able to compare these versions and generate an automated migration script.
Run the migration script on the server to upgrade the database schema, and deploy the new version of the database.