SQL Databases and Version Control–Part 1
Requirements
I imagine most developers keep their code under source control and I doubt many would argue about keeping their databases under source control, but it seems it is not as common as it should be.
There are a number of commercial offerings to support this, but they are generally overkill for my requirements.
My requirements are:
- To be able to recreate the database to any previous version.
- The database should record schema changes and versions.
- To be able to do this to SQL Server and SQL CE databases.
- To commit change / create database scripts to source control.
This does ignore a possible requirement that may be important to other developers:
- The ability to revert a database to a previous version.
The solution I am planning could possibly be extended to cover that requirement, but if that is a requirement, you may be better off looking at one of the commercial offerings.
To be able to recreate the database to any previous version
To do this, I need an initial database create script followed by any number of change scripts, all of which need to be run sequentially. The easiest way to do this (preferring convention over configuration) is to have the script file name indicate the sequence by naming it:
MajorReleaseNumber.MinorReleaseNumber.PointReleaseNumber.*.sql
The store all scripts for a given database in one folder.
The database should record schema changes and versions
This simply requires a table to store the versioning information in and a tool to run the scripts and record the changes. Additionally this allows the tool to check the current version of a database before running change scripts.
The table should contain the following columns:
| Column | Data Type | Length | Primary Key |
| Id | int | Yes | |
| MajorReleaseNumber | smallint | ||
| MinorReleaseNumber | smallint | ||
| PointReleaseNumber | smallint | ||
| ScriptName | nvarchar | 255 | |
| DateApplied | datetime |
Lastly, the table should be created in the first script run after database creation.
To be able to do this to SQL Server and SQL CE databases
This will be reflected in the tool but also affects the scripts as SQL CE is limited to a subset of the functionality of SQL Server.
To commit change / create database scripts to source control
As the database creation / changes will be done by a number of scripts this can easily be managed by the same source control system used for the code base.