Synchronize a SQL Server Database with an SVN Repository

Yes, it is possible to automatically synchronize a SQL Server database with an SVN repository. Every change to a table, stored procedure or other database object will automatically be committed to the repository. New objects will get added, deleted objects deleted, etcetera. As a bonus, why not monitor the contents of (some) database tables ? You probably have a few tables that contain parameter values that influence the applied business logic ? It may be useful to put their contents under source control too!

The basic ingredients to the solution are:

  • a vbs script that generates SQL create commands for every object in the database
  • a sql script that generates SQL insert statements for selected tables
  • a sql script that checks changes to the database since last run and creates the necessary SVN client commands

These scripts are scheduled to run on a regular basis via SQL Agent. In my case I run them nightly.

SQLAgentJob for SVN sync

Step 1: Create SQL for database objects
A VBS script uses SQLDMO objects and their capacity to generate SQL. It loops over the most “interesting” SQL Server object types – tables, views, stored procedures, functions, rules and defaults – and creates a file for each. The file name is constructed as follows: database_name.object_name.object_type.sql. (It is prefixed with the database name so that different SQL databases holding objects with the same name can be uploaded to a common repository.)
The VBS script (download it here) is launched via a SQL Agent job step of the type Operating system (CmdExec). It takes 3 parameters:

  • -w sql_output_dir: the location where the files will be saved. Use a dedicated directory for this: the script starts by deleting all files in here!
  • -s server: the database server
  • -d database: the database to script

Step 2: Create SQL for monitored database contents
This step may be optional for many. To put content from some tables under source control – I have parameter values and sql statements in tables for example – you will need the following two scripts:

GenerateContents.sql actually calls sp_CreateDataLoadScript, see my post here or have a look at the code directly here.

Step 3: Synchronize with SVN repository
This is the tricky part. There is no way to tell SVN to “just check this directory and update the repository with whatever changes that were made” where “whatever changes” can be modifications, new files or deleted files. Adding new files can be somewhat automated by issueing an SVN add * –force command: that will add all new files to the repository without enumerating each individually. Modified files will get updated via the SVN commit command. But deleted files will still remain in the repository.
Therefore it is necessary to keep track of database changes since last commit and create the necessary SVN commands accordingly.
To keep track of these changes you will need to create the following table:

CREATE TABLE L_Last_Sysversions (
    name varchar(128),
    id int,
    modify_date datetime,
    type char(2),
    filename varchar(255)
)

Each time the SQL Agent job runs, step 3 will save the current state of the SQL Server objects in this table.
Step 3 again consists of two scripts:

  • checkDBChanges.bat: a bat file that launches a sql script, captures its output and then runs that output. The output being the necessary SVN commands!
  • checkDBChanges.sql: an sql script that checks what was changed since it last ran, saves the new state of the SQL Server objects and outputs SVN commands.

Since checkDBChanges.sql outputs SVN commands it needs to know a few things about the SVN setup: where the client was installed, the user name and password to use and the location of the working copy (i.e. where steps 1 and 2 saved their output). I have these in a parameter table in SQL, but you could just as well “hard-code” these. Just have a look inside checkDBChanges.sql and change as needed, both options are there.

Getting it running on your server

0. Get all the scripts neatly zipped here and unzip in a dedicated directory on the database server, e.g. C:\DBVersioning\scripts. (*)
1. Prepare SVN: create a directory to hold the sources on your SVN repository either via svn mkdir or via the Tortoise repo-browser or whatever you use to manage your SVN. Also, you may want to use a specific SVN user for this automated sync’ing.
2. Create an SVN working copy for the directory above somewhere accessible from the database server (*) and checkout the (empty) SVN directory there. Again, use whatever SVN tool available, e.g. svn checkout if using the command line tool.
3. Create the table L_Last_Sysversions in the database you want to monitor. The SQL for this is included in checkDBChanges.sql. It is commented-out at the top of the file.
4. Edit checkDBChanges.sql: @svn_command, @svn_user, @svn_pass and @svn_wc need a value. See the file for possible ways to set these values.
5. (optional) Edit GenerateContents.sql: if you are going to monitor the data inside some tables, then enter your database name and table names (comma seperated) here. Also, execute sp_createdataloadscript.sql to create the necessary stored procedure.
6. Create a SQL Server Agent job with 3 steps: 1) calls the script to create the output that will go into the repository, 2) (optional) creates the output for the data inside the monitored tables and 3) checks for changes and updates the SVN repository. See the image above for the actual commands to put in the job steps. Note that step 1 uses cscript to start the vbs, the other steps are merely bat files that run directly.

(*) if you cannot create these directories on the database server you will need to map drives and/or do other hocus pocus to be able to access the directory from the SQL Agent job.

To test your setup launch the job. You should see files appear in the SVN working directory. Also check the contents of L_Last_Sysversions. That should contain the current SQL Server objects. Last but not least, check your SVN repository: it should contain your first version of the SQL Server sources!

If you schedule the SQL Agent job your SQL Server database will then be versioned under SVN.

Enjoy!

11 Comments »

Comment by Bryn
2009-12-14 12:56:47

Hi

I’m trying to use this utility as it’s exactly what I need – excuse my manners, thanks very much for posting it!

The problem I’m having is that it uses SQLDMO and I’m running on a SQL 2008 and SQLDMO isn’t supported.

I’m going to have a play (I’m not a VB programmer but have access to a couple) to see if I can convert it to SQLSMO which is what SQL 2008 has, but in the meantime if you have a newer version that’s already written for SQL 2008 I’d love to have it please.

Or maybe you can point me in the right direction as to how to fix it for SQL 2008?

Thanks very much.

Regards
Bryn

 
Comment by Tom VdP
2009-12-29 09:35:08

Hi Bryn,
I was not aware that the script is not SQL2008 compatible. I should have taken the SMO route then. The scripts were of course build from pieces of code and examples found on the net. It started as a bit of a quick hack, but has been proven robust over time. We have been running this setup for months with daily commits without a single glitch.
When in a good mood: feel free to mail me your SMO solution, I’ll update the scripts then. ;-)
Cheers,
Tom

 
Comment by c0bra99
2010-01-13 20:39:20

You can download SQL-DMO as part of the compatibility pack here:
http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en

Or get a smaller part of the compatibility pack towards the center of that page look for “Microsoft SQL Server 2005 Backward Compatibility Components” yes, it says 2005 but it also is for sql 2008.

 
Comment by JB
2010-03-16 22:04:13

I’m trying to implement this solution and have two issues:

1) Every time I execute the SQL Job (only steps 1 & 3) I run into a problem with the working directory being ‘locked’. I have to manually run ’svn cleanup’ each time to clear the error. Something is causing the lock and not releasing.

2) This is more of a question about executing this on several databases at once. I can’t find a way to get around having to create two sql agent step for each database. For now I’m only trying this on three databases, but would like to include all 88 databases eventually. I would also like to include step 2 above for keeping track of some table data, but wanted to get the main function working first (steps 1 & 3). Is there an easier way to get this running on multiple databases on the same instance without creating so many SQL Agent steps?

Comment by Tom VdP
2010-04-09 13:50:08

1) Sorry I do not have the locking error.
2) I have just a few monitored databases: for each there are the necessary steps in one SQL Agent job.

 
 
Comment by Reed
2010-06-08 18:04:26

Can this solution be used to update one environment, e.g. prod, with the committed changes from another environment, e.g. test? In other words, is it completely analogous to managing application code using SVN?

 
Comment by Scott
2010-06-17 19:13:24

This solution sort of approaches the problem in the same way that i have seen it addressed all over web; namely to create a bunch of CREATE scripts and put those scripts under version control. This is good for maintaining a master copy of the db that can be used to re-create the schema from scratch or setting up a new installation without data. but doesn’t this just automate what i can already do in the mgmt studio UI?

This for me does not address the need to have schema changes compared/merged/updated and conflicts resolved like you get from equivalent tools on application source files using SVN

for example:

I have three developers each with their own local copy of SQL Server 2008 Express edition (does not support SQL server agent btw ) where each developer could make changes via the visual designer of mgmt studio (they do not write alter scripts). let’s say user1 drops one column and changes the name of another column in a table with 100K rows where there are numerous foreign and primary keys , indexes etc…
user2 needs to get notified of (sent) the exact changes made by user1 so that he can sync his local copy, this is the equivalent of updating to HEAD.

how does having a freshly written CREATE script in SVN help with this? user2 can’t work effectively
if on every schema change he must drop and recreate the table loosing his test data! what a headache

I have been beta testing a new tool called SQL SOURCE Control from Redgate (http://www.red-gate.com/products/SQL_Source_Control/index.htm) that handles this problem beautifully.. It layers on top of Mgmt studio and auto commits/updates changes to and from an SVN repository. The problem is they have yet to release pricing and i fear it will be expensive.
ALso, if want to use the repository to deploy/sync from SVN to a staging or production server you are bound to using their schema comparison tool — SQL COMPARE to interpret the schema changes from a “checked-out scripts” folder and the target db. This tool is also not cheap either and is per user licensed.

I am just wondering if anybody else has addressed this issue and how they approach these challenges.

 
Comment by Bernardo Salguero
2011-06-10 22:26:57

It rocks. I use it in SQL 2008 R2 in an external server. It will need a little changes in SQL authentication for user and password. Good job!!

 
Comment by AD
2011-08-18 02:51:19

You are a legend. I used your suggested autocommit.bat for Windows to do the automatic scheduled commit. I have also written a blog about autogenerating the scripts and then commiting using the suggested autocommit.bat on http://mlifeco.tumblr.com/post/9027276260/how-to-automate-generate-script-in-sql-server — its for windows only and uses the SQL Publishing wizard to automatically generates scripts from command line — this means you can do the whole process of generation and commiting through the command line and task scheduler — without actually having the need for SQL Server Agent aka a full blown version of SQL Server Management Studio.

 
Comment by Benjamin
2012-01-20 22:52:09

Hi,

I have a Server with collation “SQL_Latin1_General_CP1_CI_AS” and a Database with collation “Modern_Spanish_CI_AS”, and a get this error when i execute the Step 3:

Msg 468, Level 16, State 9, Server ABC, Line 96
Cannot resolve the collation conflict between “Modern_Spanish_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

Help me please, thanks

 
Comment by Abh
2014-07-18 10:54:22

I have been trying this for few days, but couldnt succeed. As i am new to SQL server I request you all who can help me to provide step by step a clear way to achieve source control through tortoisesvn…
Thanks You very much.

Regards

 
Name (required)
E-mail (required - never shown publicly)
URI
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Trackback responses to this post