Source controlling data

SQL Source Control enables you to source control your database schema.

It is also possible to keep data in source control, alongside the schema, by using SQL Source Control with SQL Data Compare Professional Edition

It is planned to support source controlling data within SQL Server Management Studio in later versions of SQL Source Control.

To source control your databases, SQL Source Control creates a folder of scripts representing your database schema.

This scripts folder shares the format used by scripts in SQL Data Compare, and so the two tools can be used to source control data.

Example: source controlling data

In this example the database WidgetDev is already in source control, and we will additionally source control its static data.

This example uses the SQL Data Compare and Subversion command line interfaces.

It is also possible to source control static data using the SQL Data Compare graphical user interface, and a source control client such as TortoiseSVN

The example has 3 stages:

1. Create a local copy
2. Update the local copy with data
3. Commit the data to source control

1. Create a local copy

First, create a local copy of the database in source control by performing a Subversion checkout.

At a command prompt, type:

cd C:\program files\subversion\bin

svn checkout http://svn-eval.red-gate.com/svn/<project>/trunk/WidgetDev "C:\WidgetDevScripts"

Where:

• http://svn-eval.red-gate.com/svn/<project>/trunk/WidgetDev is the URL for the database in your Subversion repository
• "C:\WidgetDevScripts" is the file path for the directory where the local copy will be created

A local copy of the scripts folder is created. This is a Subversion working copy, and is associated with the subversion repository.

2. Update the local copy with data

Use SQL Data Compare to update the scripts folder with data.

In this example the table WidgetPurchases, which contains transactional data, is excluded.

At a command prompt, type:

cd C:\program files\red gate\SQL Data Compare 8

/sqldatacompare /db1:"WidgetDev"

/scr2:"C:\WidgetDevScripts"

     /Exclude:table:WidgetPurchases

     /sync

Where:

• /db1:WidgetDev specifies WidgetDev as the source
• /scr2:"C:\WidgetDevScripts" specifies the local copy, WidgetDevScripts, as the target for a SQL Data Compare synchronization
• /Exclude:table:WidgetPurchases excludes WidgetPurchases. All other tables will be included
• /sync performs a SQL Data Compare synchronization, making the data in WidgetDevScripts the same as WidgetDev

The local copy is updated with data.

This example assumes you are using Windows authentication to log into the database. You may need to supply a user name and password.

For more information, see Getting started with the SQL Data Compare command line

3. Commit the data to source control

The final step is to commit the changes from the local copy to source control.

At a command prompt, type:

cd C:\program files\subversion\bin

svn commit C:\WidgetDevScripts

You may be prompted to supply a comment when committing.

Source control is updated.

Automating the process

If the data you want to source control is likely to change during development, you can save these commands as a .bat file, making it easier to commit changes.

The following example re-uses the local copy we have already created, so you do not need to perform a Subversion checkout. Instead the folder is updated with the latest changes.

The example includes a commit comment.

Save the following:

cd C:\program files\subversion\bin

svn update "C:\WidgetDevScripts"

cd C:\program files\red gate\SQL Data Compare 8

/sqldatacompare /db1:"WidgetDev"

/scr2:"C:\WidgetDevScripts"

     /sync

cd C:\program files\subversion\bin

svn commit C:\WidgetDevScripts

     -m "updated static data"

You can run this script to automatically commit data changes, or automate it using the Scheduled Task Wizard.