How to version control SQL Server databases?

Question

I have SQL Server databases and do changes in them. Some database tables have records that are starting records required my app to run. I would like to do version control over database and these records (rows). Is it possible to do this and bundle it to SVN version control I have for my source code or are there other solutions to this? I would like to accomplish this to be able to return to previous version of database and compare changes between database revisions. It would be nice if tools for this are free, open source or not very expensive.

My environment is Visual C# Express, SQL Server 2008 Express and Tortoise SVN.

Answer 1

Late answer but hopefully useful to other readers

I can suggest using the SSMS add-in called ApexSQL Source Control. By utilizing this add-in, developers can easily map database objects with the source control system via the wizard directly from SSMS. It includes support for Git, TFS, Mercurial, Subversion, TFS (including Visual Studio Online) and other Source Control systems. It also includes support for source controlling Static data (so you can version control records also).

After downloading and installing ApexSQL Source Control, simply right-click the database you want to version control and navigate to ApexSQL Source Control sub-menu in SSMS. Click the “Link database to source control” option and select the source control system and the database development model, for example:

After that, you may exclude objects you don’t want to be linked to source control. It is possible to exclude specific objects by owner or type.

On the next step, you will be prompted to provide the log-in information for the source control management system:

Once done, just click the “Finish” button and the “Action center” window will be shown, offering the objects that will be committed to the repository (this is by default, if the repository is empty).

Once the database has been linked to source control, all the operations that can be executed from a source control client will be available from the “Object Explorer” pane. Those include:

• checking out with or without lock the versioned objects,
• view history of that object and apply specific revision,
• view changes on that object that were made and
• place data from table to source control using the “Link static data”

You can read this article for more information: http://solutioncenter.apexsql.com/sql-source-control-reduce-database-development-time/

Answer 2

Static data support is being added to SQL Source Control 2.0, currently available in beta. More information on how to try this can be found here:

http://www.red-gate.com/messageboard/viewtopic.php?t=12298

Answer 3

This project has a good example of deploy and rollback

Answer 4

I use bcp for this (bulk loading utility, part of a standard SQL Server install, Express edition included).

Each table with data needs a control file Table.ctl and a data file Table.csv (these are text files that can be generated from an existing database using bcp). As text files, these can very easily be versioned.

As part of my generation batches (see my answer there for more information), I iterate through every control file like this :

SET BASE_NAME=MyDatabaseName
SET CONNECT_STRING=.\SQLEXPRESS

FOR /R %%i IN (.) DO (
  FOR %%j IN ("%%~fi\*.ctl") DO (
    ECHO +   %%~nj
    bcp %BASE_NAME%..%%~nj in "%%~dpsj%%~nj.csv" -T -E -S %CONNECT_STRING% -f "%%~dpsj%%~nj.ctl" >"%TMP%\%%~nj.log"
    IF %ERRORLEVEL% GTR 0 (
      TYPE "%TMP%\%%~nj.log"
      GOTO ERROR_USAGE
    )
  )
)

A current limitation of this script is that the name of the file must be the name of the table, which may not be possible if the table name contains specific special characters.

Answer 5

For database (schema) versioning we use custom properties, which are added to the database when the installer is ran. The contents of these scripts is generated with our build scripts.

The script to set the properties looks like this:

DECLARE @AssemblyDescription sysname
SET @AssemblyDescription = N'DailyBuild_20090322.1'

DECLARE @AssemblyFileVersion sysname
SET @AssemblyFileVersion = N'0.9.3368.58294'

-- The extended properties DatabaseDescription and DatabaseFileVersion contain the
-- AssemblyDescription and AssemblyFileVersion of the build that was used for the
-- database script that creates the database structure.
-- 
-- The current value of these properties can be displayed with the following query:
-- SELECT * FROM sys.extended_properties

IF EXISTS (SELECT * FROM sys.extended_properties WHERE class_desc = 'DATABASE' AND name = N'DatabaseDescription')
BEGIN
    EXEC sys.sp_updateextendedproperty @name = N'DatabaseDescription', @value = @AssemblyDescription
END
ELSE
BEGIN
    EXEC sys.sp_addextendedproperty @name = N'DatabaseDescription', @value = @AssemblyDescription
END

IF EXISTS (SELECT * FROM sys.extended_properties WHERE class_desc = 'DATABASE' AND name = N'DatabaseFileVersion')
BEGIN
    EXEC sys.sp_updateextendedproperty @name = N'DatabaseFileVersion', @value = @AssemblyFileVersion
END
ELSE
BEGIN
    EXEC sys.sp_addextendedproperty @name = N'DatabaseFileVersion', @value = @AssemblyFileVersion
END
GO

Answer 6

There is a free microsoft product called Database Publishing Wizard which you can use to script the entire database (schema and data). It is great for taking snapshots of the current state of a DB and will enable you to recreate from scratch at any point

Answer 7

You should rather use DB specific versioning.

http://msdn.microsoft.com/en-us/library/ms189050.aspx

When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, logical copies (versions) are maintained for all data modifications performed in the database. Every time a row is modified by a specific transaction, the instance of the Database Engine stores a version of the previously committed image of the row in tempdb. Each version is marked with the transaction sequence number of the transaction that made the change. The versions of modified rows are chained using a link list. The newest row value is always stored in the current database and chained to the versioned rows stored in tempdb.

Answer 8

We've just started doing the following on some of our projects, and it seems to work quite well, for populating "static" tables.

Our scripts follow a pattern where a temp table is constructed, and is then populated with what we want the real table to resemble. We only put human readable values here (i.e. we don't include IDENTITY/GUID columns). The remainder of the script takes the temp table and performs appropriate INSERT/UPDATE/DELETE statements to make the real table resemble the temp table. When we have to change this "static" data, all we have to update is the population of the temp table. This means that DIFFing between versions works as expected, and rollback scripts are as simple as getting a previous version from source control.

The INSERT/UPDATE/DELETEs only have to be written once. In fact, our scripts are slightly more complicated, and have two sets of validation run before the actual DML statements. One set validate the temp table data (i.e. that we're not going to violate any constraints by attempting to make the database resemble the temp table). The other validate the temp table and the target database (i.e. that foreign keys are available).

Answer 9

MS Visual Studio Team System for Database Developers has functionality to easily generate create scripts for the whole schema. Only drawback is the cost!

Have you considered using SubSonic?

Answer 10

You can get a version of SQL Management Studio for SQL Server Express. I believe you'll be able to use this to produce scripts of the schema of your database. I think that will leave you to create scripts by hand for inserting the starting records.

Then, put all the scripts into source control, along with a master script that runs the individual scripts in the correct order.

You'll be able to run diffs using windiff (free with Visual Studio SDK), or else Beyond Compare is inexpensive, and a great diff/merge/sync tool.