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

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.

Answer 2

See this question: http://stackoverflow.com/questions/4952/database-version-control

Answer 3

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 4

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 5

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 6

Check this link

http://www.codeproject.com/KB/database/DBScriptSafe.aspx

Answer 7

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 8

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 9

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 10

Red Gate has recently released SQL Data Compare 8 Pro with which you can script out static data tables to a file system (and therefore source control). The tool then can be used to deploy these changes to a target server.

At some point we plan to integrate this feature into SQL Source Control, although this may not be for its initial v1.0 release as our priority is to get the schema nailed down.

David (Product Manager at Red Gate)

Answer 11

This project has a good example of deploy and rollback