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?

Solution 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.

Solution 2

Red Gate's SQL Compare product not only allows you to do object-level comparisons, and generate change scripts from that, but it also allows you to export your database objects into a folder hierarchy organized by object type, with one [objectname].sql creation script per object in these directories. The object-type hierarchy is like this:

\Functions
\Security
\Security\Roles
\Security\Schemas
\Security\Users
\Stored Procedures
\Tables

If you dump your scripts to the same root directory after you make changes, you can use this to update your SVN repo, and keep a running history of each object individually.

Solution 3

This is one of the "hard problems" surrounding development. As far as I know there are no perfect solutions.

If you only need to store the database structure and not the data you can export the database as SQL queries. (in Enterprise Manager: Right click on database -> Generate SQL script. I recommend setting the "create one file per object" on the options tab) You can then commit these text files to svn and make use of svn's diff and logging functions.

I have this tied together with a Batch script that takes a couple parameters and sets up the database. I also added some additional queries that enter default data like user types and the admin user. (If you want more info on this, post something and I can put the script somewhere accessible)

If you need to keep all of the data as well, I recommend keeping a back up of the database and using Redgate (http://www.red-gate.com/) products to do the comparisons. They don't come cheap, but they are worth every penny.

Solution 4

First, you must choose the version control system that is right for you:

• Centralized Version Control system - a standard system where users check out/check in before/after they work on files, and the files are being kept in a single central server

• Distributed Version Control system - a system where the repository is being cloned, and each clone is actually the full backup of the repository, so if any server crashes, then any cloned repository can be used to restore it After choosing the right system for your needs, you'll need to setup the repository which is the core of every version control system All this is explained in the following article: http://solutioncenter.apexsql.com/sql-server-source-control-part-i-understanding-source-control-basics/

After setting up a repository, and in case of a central version control system a working folder, you can read this article. It shows how to setup source control in a development environment using:

• SQL Server Management Studio via the MSSCCI provider,

• Visual Studio and SQL Server Data Tools

• A 3rd party tool ApexSQL Source Control

Solution 5

Here at Red Gate we offer a tool, SQL Source Control, which uses SQL Compare technology to link your database with a TFS or SVN repository. This tool integrates into SSMS and lets you work as you would normally, except it now lets you commit the objects.

For a migrations-based approach (more suited for automated deployments), we offer SQL Change Automation (formerly called ReadyRoll), which creates and manages a set of incremental scripts as a Visual Studio project.

In SQL Source Control it is possible to specify static data tables. These are stored in source control as INSERT statements.

If you're talking about test data, we'd recommend that you either generate test data with a tool or via a post-deployment script you define, or you simply restore a production backup to the dev environment.

View more solutions

Comments

[Alex S]

Version controlling full schema scripts is very useful for reference purposes. For instance, it is impossible to see what exactly was changed in a stored procedure by looking at ALTER PROCEDURE statement.

[Silvercode]

So there should be a system that tracks what columns you are changing and remember the mappings from old column names to the new column names.

[sam1132]

Dumping (and versioning) the full DB schema after running new upgrade scripts is a good way to make information available to other tools in your build/deploy process as well. Also, having the full schema in a script means being able to "spin up" a fresh database without going through all the migration steps. It also makes it possible to diff the current version against accumulated previous versions.

[A-K]

Are saying that you put upgrade scripts in source control, nut do not put rollback ones there?

[nikc.org]

I have a habit of maintaining a full create and drop script, as well as delta scripts for updating existing db instances up to date. Both go into version control. The delta scripts are named according to revision numbers. That way it's easy to automate db patching with an update script.

[penderi]

I've used DbGhost for 10 years and it's never let me down. The support they provide is second to none

[David Atkinson]

We've just released SQL Source Control, which integrates the SQL Compare behavior you describe into SSMS, and links to SVN and TFS. I've added a separate answer to this question that describes in more detail what it does. red-gate.com/products/SQL_Source_Control/index.htm

[annakata]

Interesting product (bit of a gap in the market) but deltas stored as "CREATE..." scare me. How are you branching/merging?

[David Atkinson]

We store the object definitions as CREATE, but if you 'get latest' or, for example, use SQL Compare Pro to generate sync scripts, these get changed to the appropriate commands, such as ALTER. To branch or merge, you'd simply use your source control system the same way as you currently do.

[Taichman]

regarding the data - you can use OffScale DataGrove to save versions of your entire DB (data included). You can later use it to spawn up two virtual copies of your DB which can be compared with red-gate's product. It also saves you the need to generate test data - you can just save versions of the DB to match the different test-cases (again, full, virtual copies of the entire DB)

[Silviu-Marian]

@nikc.org's answer, plus post-commit hooks for automation.

[Jamie Kitson]

How do you work out which order to run the database scripts if you use the "one file per object" option?

[dariol]

After some years I say: Use FluentMigrator (or similar tool for your platform).

[Jez]

What's really frustrating is that a VS database project can generate the scripts needed for these schema updates, but it will directly apply them. It won't handle the version history stuff. You have to manually do that, and if you even once forget to manually add the update script you're screwed because your DB will already be updated and the DB project won't generate that same update script again. I wish there were a project type that handled versioned database management.

[Jez]

Yeah but unfortunately you have to remember to "generate script" every single time. If you directly update the database, you lose the ability to generate the update script for that delta. If only database projects would have some built-in functionality for versioning.

[Atario]

I do this too, but I keep an ordinary backup file in source control in place of a full-generation script. Faster and less problem-prone, in my experience. It does make it slightly harder to diff between arbitrary versions, but then again doing this doesn't come up that often, and the update scripts are perfectly searchable anyway, which should suffice, since all changes go through them. Also, for version numbers inside the DB itself, I put an Extended Property on the database object. This way it's part of the schema instead of part of the data.

[Victor Zakharov]

@Taichman: DataGrove does not seem to support SQL server, and as such has no relevance to the question.

[Peter Henell]

We use Liquibase in 5 distributed teams on a single branch for continuous delivery and it is working great. We have 10+ database applications installed on many different environments. We use it to manage schema, indexing, partitioning, code, lookup data, groups and group permissions. We use it for Oracle, Postgresql and MSSQL.

[JDPeckham]

If i understand correctly based on the intro, it requires you know some proprietary xml language to declare your objects instead of SQL? Not a fan.

[David Atkinson]

It's worth bearing in mind that for database changes that have ambiguity (and therefore need an element of "developer intent"), a migrations-based solution is the appropriate solution. Redgate now has ReadyRoll that satisfies this versioning approach.

[WonderWorker]

This answer is a duplicate of Dane's answer posted two years earlier.

[David Atkinson]

It's a different answer. SQL Compare doesn't version control databases, whereas SQL Source Control was designed specifically for that.

[Newbie]

What if I want to roll back the database to an earlier version?

[Andrew Borst]

Azure Data Studio has database projects, too.