Phil Factor

27 February 2020

• SQL Compare
• Database Builds and Deployments

Build and fill a SQL Server Database using SQL Compare CLI and a Batch Script

Imagine you need to build the latest version of your database, stock it with the data you need for testing, and distribute several copies of it. By far the fastest and easiest was to do it, especially if the database is large, is to use SQL Clone. Alternatively, if you use SQL Toolbelt, you could build each of the five copies from a source directory, fill them with standard test data sets, via BCP, then use SQL Change Automation and some PowerShell (or the SQL Compare CLI and PowerShell) to deploy them.

However, there are clearly still Ops people who prefer to use DOS scripts over PowerShell, as the surprising popularity of one of my recent blogs on the topic will attest. They are familiar with it and often still maintain a vast archive of existing DOS scripts. Can one, I mused, create a DOS batch script that does the same job? No PowerShell, no SMO, just SQL Compare, SQLCMD and BCP. If you yearn for a bit of excitement and suspense, please skip over the next paragraph.

For the rest of us, I can reveal the ‘spoiler’ that the answer is ‘yes’. An article that recounts a painful failure and head-scratching might be an interesting novelty on a product learning website, but it won’t happen here. I’ll explain how and why I wrote this demonstration code.

DOS Batch Scripts

It is hard to imagine, at first glance, a less promising scripting language than DOS Batch, though it is uniquely suited to file-system tasks. Its roots go back to UNIX via CP/M and MSDOS. It is still there in MSDOS. I’ve had to use it many times over the years to perform tasks for which there were no realistic alternatives. Surprisingly, it still attracts a small but dedicated following. For an up-to-date and interesting collections of examples, see ‘Rosetta Code: Category:Batch file’ and DosTips – The DOS Batch Guide. The Rosetta Code site allows you to compare the code of different languages as they perform standard algorithms and tasks.

Aside from the warm, nostalgic feeling it gives some admins, there are occasionally times, such as shared database tenancy, or in creating databases with special datasets for testing, where a DOS-based method like this can be more convenient.

More to the point, almost every release management tool, build automation tool, deployment workflow system, configuration management or Continuous integration tool allows you to execute DOS batches. It is as close to the universal scripting language as we have, despite the pre-eminence of PowerShell on Windows.

The task of building and filling a database

During development and test, you’ll often need to create one or more copies of a database build, stocked with the specific data you need for testing. You are likely to need to maintain several datasets. For example:

• You may not be allowed access to the real data. There are plenty of good reasons for this. It may be private or confidential. It might be illegal. Therefore, you’ll need a dataset that is generated to be as close as possible in its nature and distribution to the real data, or is a masked, anonymized version of the real data.
• You may be supporting a series of tests that require a standardized data set that is used every time to enable the testers to do black-box testing.
• You could be doing scalability tests to make sure that the design of database performs well with different amounts of data.

We need an automated way to build each of the working database copies. As discussed, while there are easier and faster ways to do it, we’re going to use a DOS batch file. It will build a database from a source directory, using SQL Compare, and then fill it with data from an existing directory of BCP Files. To do this, you provide the name of a root directory and it stores the data under subdirectories representing the server and database. It creates these directories as necessary. The DOS script contacts the newly built database, gets a list of tables, and BCPs the data from the source directory to the target.

Having created the initial working copy, we can then create subsequent copies by using it as a source database. The DOS batch script will check the destination and delete any existing database of the same name, before creating an empty target database. It then uses SQL Compare to synchronize the schema of the empty target with the source, and then finally copies over all the data from the source database.

There are, along with many seemingly simple tasks, certain complications. Target servers need to be appropriately set up with the right extensions, such as full-text search, to meet the requirements of the database. A database may have requirements that require special filters or switches. With SQL Compare, we can use ArgFiles to refine the script to allow this. I show a way of slipping in configurable parameters in BCP because I need to have a device like this to allow for ID/Password authentication.

If tables or databases have illegal names with spaces in them, this must be dealt with. SQL Server instance names too require modification, if you use them as directory names when storing files.

One of the more awkward tasks is that of reading any required IDs and passwords from disk. It is horrible to keep these in a script. You must, at least, keep them in files in your user area where NTFS security should keep them away from prying eyes. In an ideal world, every application would accept and read a standard connection string, but sadly every CLI application seems to have a different way of doing this, so the credentials must be parsed and correctly laid out for the command line.

Running the Code

For the purposes of the article, the script has been laid out stream-of-consciousness-style to make it easier to follow, though it is easy to make it into a function if you have a lot of jobs to do.

Testing it all out

Running this is pretty simple. What you do depends on the task. You can, of course, set up different versions of the batch file for each task, or go for the lower-maintenance option of remembering the configuration options that are commented in the source.

1. Create target database from source directory without data

At the start of the script, Fill in the path to the source directory as the value of the variable SourceScriptsDirectory, add the name of the target database to Target and the Target server to TargetServer, and the specify in the workpath file location the path to the directory containing the native BCP data files. Set output to no and set input to no (meaning don’t copy data from the source or into the target).

2. Create target database from source directory, fill with data from BCP directory

Fill in, at the start of the script, the path to the source directory, in the variable SourceScriptsDirectory, add the name of the target database to Target and the target server to TargetServer. Specify in the workpath file location, the path to the directory containing the native BCP data files. Set output to no and set input to yes (meaning don’t copy data from the source but copy it into the target)

3. Create target database from source directory, fill with data from source database

Fill in, at the start of the script, the path to the source directory in the variable SourceScriptsDirectory, add the name of the target database to Target and the target server to TargetServer. Specify in the workpath file location, the path to the directory containing the native BCP data files. Set output to yes and set input to yes (meaning copy data from the source and copy it into the target).

4. Synch a target database with a source database, BCP in data

You merely fill in, to the start of the script, the name of the source database and server, the target database and server, and the workpath file location to put the native BCP data files. Set output to yes and set input to yes.

5. Synch a target database with a source database, without data

As before, you fill in, to the start of the script, the name of the source database and server, the target database and server, and the workpath location. Set output to no and set input to no.

Handling login credentials

Unless you want to use your Windows login for the operation, you also need to write your SQL Server credentials into a file, one for each servers that you use to the root of your user area as I’ve indicated in the source code. This only must be executed only once, and then you should delete the code! I’ve provided the source code to do this:

Just delete the REM keywords, add your UserIDs and credentials and all should be well. Don’t forget to delete the code once you’ve executed it.

Running the DOS batch script

Open a command prompt and type in the name of the batch file, including the path, and off it goes. Such is the nature of DOS files that things can easily go awry but there shouldn’t be too many problems with this code. To debug, first delete the @echo off the first line so you can see how the batch ran. There are more hints here.

Here is the typical output, execution just completed:

When the script has run, and you have included the BCP OUT operation (output=true) you should see the files in their directory here:

If you fire up SQL Data Compare, it will tell you that the data in both copies of the database are the same. And SQL Server will say the same about the metadata.

Conclusions

The joy of applications that can run in DOS as CLI applications is that you can choose from a wide range of scripting languages and methods. Although I like PowerShell, there are plenty of people around, especially in Ops, who actually prefer the DOS batch language because it is so near to the operating system, it is used on a large library of existing scripts, and it runs without needing a special scripting environment.

SQLCMD is such a powerful system that you can do a lot of things with it easily that take effort in any other way. Even the database developer can find DOS to be a convenient way of slinging scripts together with the help of a library of commonly used snippets. If you combine this with database tools with a CLI interface, such as SQL Compare, SQL Data Compare, or SQL Data Generator, and you have a pretty powerful toolkit for creating your latest development builds and filling them with the specific datasets you need.