Get your static data under control with SQL Data Compare 8’s early access build

Usability tests and research have shown us that maintaining database schema and static data in source control can be a real headache. Instead of reaching for the aspirin, how about trying out SQL Data Compare 8 for some pain relief?

You can download our early access build here:

http://www.red-gate.com/MessageBoard/viewforum.php?f=96

There’s more on what this build contains and the known issues here:

http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Data%20Compare&c=SQL_Data_Compare/help/8.0Early_access/SDC_GettingStartedEAR.htm

The ability to create, compare, and synchronize to and from scripts folders has long been a feature of SQL Compare (since version 6), so why would analogous functionality be useful for data?

We’re not suggesting that you script out your entire production database, but if your applications include static data (lookup tables, reference data, configuration data, etc) and you need to version control your database, it makes perfect sense to store your schema and static data together as a single structure. Once your database is under source control it’s easy to migrate changes and compare versions: seeing the differences is much easier with scripts of INSERT statements than when comparing backup files.

In this post I’ll show you how easy it is to save your database as a set of scripts, ready to go straight into source control.

First off, set up a new project in SQL Data Compare.

The more observant among you may notice that we’ve brought over quite a bit of the UI shininess from SQL Compare 8.

I want to create a set of scripts from my WidgetDev database, so I’ve set up the source (left-hand side) with the details for the server.

For the target (right-hand side), I’ve selected the new ‘Scripts Folder’ radio button.

First we need to create the schema. You can do this in either SQL Compare or SQL Data Compare; I’m going to use SQL Data Compare in this example. I’ve put a new empty folder (C:\WidgetDev) on the right hand side of the comparison. Then I can click the ‘Create Schema Scripts’ button: SQL Data Compare will take the schema from the left-hand side and create the SQL scripts in the folder.

So now my scripts folder looks like this:

There’s a folder for each object type (in this case one for Tables and one for Views), with a SQL file for each individual object.

The file representing the Widgets table (dbo.Widgets.sql) looks like this:

Now that we’ve got our schema, we can look at doing the same for our data. We’re only interested in migrating static data, so let’s go into the Tables and Views tab to select the relevant tables.

I’ve unchecked the WidgetPurchases table, which contains the transactional data. The other tables contain static data that I want to script out. We can go ahead and Compare Now…

From here we can see the rows from the WidgetDev database that will be migrated into our scripts folder. Click the Synchronization Wizard button.

We’re going to let SQL Data Compare populate the scripts folder, so choose the ‘Update the scripts folder’ option and click Next.

Here you can see all the new files that will be created. Click Synchronize Now and we should be done.

If we look at our scripts folder we can see how the data has been added:

There’s now a data folder for each table containing a file of SQL DML statements. Let’s take a look at the data in the Widgets table:

The process is finished and we’ve created a folder representing our WidgetDev database, with schema and data. Now we can check that into source control or use it directly with SQL Data Compare to migrate changes to the production server, exactly as we would with any other database.