How to do version control for SQL Server database?

Question

I want to get my databases under version control.

I'll always want to have at least some data in there (as alumb mentions: user types and administrators). I'll also often want a large collection of generated test data for performance measurements.

How would I apply version control to my database?

Answer 1

Martin Fowler wrote my favorite article on the subject, http://martinfowler.com/articles/evodb.html. I choose not to put schema dumps in under version control as alumb and others suggest because I want an easy way to upgrade my production database.

For a web application where I'll have a single production database instance, I use two techniques:

Database Upgrade Scripts

A sequence database upgrade scripts that contain the DDL necessary to move the schema from version N to N+1. (These go in your version control system.) A _version_history_ table, something like

create table VersionHistory (     Version int primary key,     UpgradeStart datetime not null,     UpgradeEnd datetime     ); 

gets a new entry every time an upgrade script runs which corresponds to the new version.

This ensures that it's easy to see what version of the database schema exists and that database upgrade scripts are run only once. Again, these are not database dumps. Rather, each script represents the changes necessary to move from one version to the next. They're the script that you apply to your production database to "upgrade" it.

Developer Sandbox Synchronization

1. A script to backup, sanitize, and shrink a production database. Run this after each upgrade to the production DB.
2. A script to restore (and tweak, if necessary) the backup on a developer's workstation. Each developer runs this script after each upgrade to the production DB.

A caveat: My automated tests run against a schema-correct but empty database, so this advice will not perfectly suit your needs.