Automatically Filling SQL Server Test Databases With Data

This article explains how you can use SQL Data Generator (SDG) to automate data provisioning for test databases, during the database development cycle.

Having first created an SDG project file for a database, to define the data generation strategy, we can write a command line batch script, or PowerShell script, which uses a build script in source control, to create a copy of the database in the test environment, and then the SDG project file to fill it with data.

There is an underlying assumption that you will never need to subsequently alter the test database because that would mean altering the SDG project file to reflect the schema changes, and for that, you need one project file per copy of the database; my next article will show how to achieve this.

However, if you just need to build a new test database, from the latest version in source control, fill it with data, run the tests, and tear it down again, then the technique shown in the article will do the job. It follows on from some preceding articles, which described how to generate realistic text, dates, and addresses for individual tables in a customer database.

Using SQL Data Generator From the Command Line

Your first task is to use the SDG’s GUI to design a strategy for filling each of the tables in your database with data.

If you already have test data, SDG can load that from a text or CSV file. If you have an existing database, you can use BCP to export the table data, and then use SDG to import that data, after whatever obfuscation or cleansing you do to it. SDG takes care of inserting it in the right order, and the other complexities of interdependent data.

Alternatively, you can just let SDG generate the data for you. This strategy can range from accepting the ‘default’ data generated by the tool to using advanced regexes to generate fake data that is almost indistinguishable from the real data (see the previously-referenced articles for some examples).

Whichever way you do it, once you’re done, simply save into source control your SDG project, to capture in the .sqlgen project file all your data generation settings and options for that database.

We can then call SDG from a batch script or from PowerShell, and SDG will use this project file to fill a new copy of the database with data, as long as it can reach the server, and your Windows login has the necessary access rights.

The .sqlgen project file is an XML file that can be read and updated in PowerShell but this is probably never necessary. Usually, the only items that need to be specified are the database and the server instance.

A Command Line 'Build and Fill' Example

You have a database that you are developing. It could be that it hasn’t yet gone live, or it might be that either you can’t use, or won’t accept the risk of using, live production data to run your tests. You have to create data or load pre-prepared data.

Every working day during development, you make changes to the database, unit test them and save them to your Version Control System. The overnight build creates the database, and fills it with data before running your integration tests. I’ll show you how to build and fill your database using SDG.

Listing 1 shows a simple PowerShell script that builds the database from a script stored in source control, then fills it with data, according to the .sglgen project file. To keep to the topic of creating the data, I’ll leave to one side how we created the T-SQL Database build script (in this example, I wrote it as one script).

pushd;
Import-Module sqlps -DisableNameChecking #load the SQLPS functionality
popd;

# Firstly, we create the aliases to make the execution of command-line programs easier.
Set-Alias SQLDataGenerator 'C:\Program Files (x86)\Red Gate\SQL Data Generator 3\SQLDataGenerator.exe' -Scope Script

# Now we set the script variables to do the job we want
$MyDatabase='Customers'
$TargetServer='MyServer' #The SQL Server instance
$MyReportDirectory="$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\GitHub\$MyDatabase\"
$MyBuildScript="$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\GitHub\$MyDatabase\$MyDatabase-build.sql"
$MySQLDataGeneratorFile="$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\GitHub\$MyDatabase\$MyDatabase.sqlgen"
# Firstly we create the script directory (normally you get these from source control
Invoke-Sqlcmd -serverinstance $TargetServer -InputFile $MyBuildScript | Out-File -filePath "$MyReportDirectory\Build.rpt"
sqldatagenerator /project:$MySQLDataGeneratorFile /server:$TargetServer /database:$MyDatabase | Out-File -filePath "$MyReportDirectory\Build.rpt"

Listing 1

You will, if all works well, have a database that is already stocked with data, ready for integration tests and performance tests.

Troubleshooting Data Generation Problems

Things can go wrong, of course. If SDG finds that it cannot, for some reason, insert the data, the whole table insertion is rolled back.

The error is not returned as a DOS error by the application. Instead, you have to check the report produced by SQL Data Generator (an extract from one is shown in Figure 1 below):

Figure 1

You can export the report to a file and search it with a regex. The most obvious way is to search for the word ‘exception’.

Conclusions

If you regularly need to stock a database with faked data, or a standard data set, in order to conduct any sort of testing, then SDG is designed to be the tool to do this. It will work happily with any Build system such as Jenkins or TFS, and is just as amenable to use with PowerShell.

I like to store a version of the SDG project with the build scripts in source control so that it is possible to build, at any time, any version of the database, along with its data. To do this, you need to add any additional files used by SQL Data Generator, along with the script, into source control.

The technique I describe in this article is fine for test databases that you don’t need to subsequently modify, but what if you want to build the latest version on your development workstation, and stock it with data?

You’ll want to be able to make subsequent alterations to the database schema, and then you’ll need to be able to adapt the data generation plan for the affected tables. To do that, though, you’ll need a .sqlgen file specifically for every development copy of the database.

That’s the topic of my next article, coming soon, Automatically filling your development databases with data.