Versioning SQL Server database

Question

I want to get my databases under version control. Does anyone have any advice or recommended articles to get me started?

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.

Answer 1

If you have a small database and you want to version the entire thing, this batch script might help. It detaches, compresses, and checks a MSSQL database MDF file in to Subversion.

If you mostly want to version your schema and just have a small amount of reference data, you can possibly use SubSonic Migrations to handle that. The benefit there is that you can easily migrate up or down to any specific version.

Answer 2

We just started using Team Foundation Server. If your database is medium sized, then visual studio has some nice project integrations with built in compare, data compare, database refactoring tools, database testing framework, and even data generation tools.

But, that model doesn't fit very large or third party databases (that encrypt objects) very well. So, what we've done is to store only our customized objects. Visual Studio / Team foundation server works very well for that.

TFS Database chief arch. blog

MS TFS site

Answer 3

Check out DBGhost http://www.innovartis.co.uk/. I have used in an automated fashion for 2 years now and it works great. It allows our DB builds to happen much like a Java or C build happens, except for the database. You know what I mean.

Answer 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

Answer 5

You didn't mention any specifics about your target environment or constraints, so this may not be entirely applicable... but if you're looking for a way to effectively track an evolving DB schema and aren't adverse to the idea of using Ruby, ActiveRecord's migrations are right up your alley.

Migrations programatically define database transformations using a Ruby DSL; each transformation can be applied or (usually) rolled back, allowing you to jump to a different version of your DB schema at any given point in time. The file defining these transformations can be checked into version control like any other piece of source code.

Because migrations are a part of ActiveRecord, they typically find use in full-stack Rails apps; however, you can use ActiveRecord independent of Rails with minimal effort. See here for a more detailed treatment of using AR's migrations outside of Rails.

Answer 6

I agree with ESV answer and for that exact reason I started a little project a while back to help maintain database updates in a very simple file which could then be maintained a long side out source code. It allows easy updates to developers as well as UAT and Production. The tool works on but Sql Server and MySql.

Some project features:

• Allows schema changes
• Allows value tree population
• Allows separate test data inserts for eg. UAT
• Allows option for rollback (not automated)
• Maintains support for SQL server and Mysql
• Has the ability to import your existing database into version control with one simple command(sql server only ... still working on mysql)

The code is hosted on google code. Please check out Google code for some more information

http://code.google.com/p/databaseversioncontrol/