Product articles SQL Change Automation Database Builds and Deployments
Recreating Databases from Scratch with…

7 October 2019

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

7 October 2019

Recreating Databases from Scratch with SQL Change Automation

Phil Factor starts with the basics how to rebuild a set of development database from scratch, using SQL Change Automation, and then demonstrates how to check for any active sessions before rebuilding, import test data using BCP, and secure passwords if connecting to the target with SQL Server credentials.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

You are using SQL Change Automation (SCA), or perhaps SQL Compare, to prepare development databases. You have the routine task of updating a list of development databases, on various instances, from the current source of the database. This isn’t exactly rocket science, but it needs to be done often, and so must be entirely automated. You don’t care what is on the existing copies of the databases, so you want to “kill and recreate”. You also want the databases correctly versioned and checked, and you need the code analysis too.

I’ll show how to automate all this, keeping things simple to start with, and ignoring the three elephants chewing meditatively on branches on the corner of the room. These are called ‘development-data’, ‘passwords-in-script’ and ‘database-in-use’. Firstly, development databases are no use without data, secondly, you can’t leave passwords in scripts and, finally, you shouldn’t and would be unlikely to want to, kill a database if it is in use.

Having demonstrated the basic principles of how we get SCA to recreate databases from scratch, I’ll show a final script that deals with the three elephants humanely.

The basic script

We’ll start the script by itemizing the target databases, using a connection string for each. At the same time, we specify the source and the details about the current build and database version.

We delete the database if it currently exists. Then we recreate each of the databases by using an SCA release object. First, we create a build artifact from the source (a single build script, or a set of object build scripts), adding to it the project name, version and description. From that, we create a release artifact. There is always one build artifact per database version, but generally we need a separate release artifact for each target. However, here, we can be certain all targets are identical, so having created it the first time, we simply reuse the same release object on each empty database.

The elephants in the room

Now, let’s look at the elephants, ‘database-in-use’ and ‘development-data’, and then the baby elephant ‘passwords-in-script’.


As we have created a connection with the server, it is easy to check whether the database is in use. We’ll simply see if anyone has an open session, and if so, we won’t delete the database. If the number is greater than zero, we can’t deploy the new version of the database, but we want to press on with the other databases because the chances are that only one or two developers are working late or have left a session open in SSMS after packing up for the day.

You can do this in SQL, as follows:

However, SMO has a built-in method of doing this called srv.GetActiveDBConnectionCount($databaseName), so we’ll use that.

One precaution of checking for existing connections is that we mustn’t leave our own connections lying around when we use the PowerShell IDE, so we need to close our own connections. This isn’t necessary with the command line script as it is done automatically by .NET when the PowerShell session ends.


I admit that where each database requires a lot of data, it saves a lot of time and space to use SQL Clone for this. You build-and-fill one database, then distribute clones of it to all the development instances. Otherwise, we can just use SCA and store the data in a separate directory as native BCP files. You can do it other ways if you don’t mind waiting longer.

I use a PowerShell script to read BCP data out of a database (see Scripting out SQL Server Data via PowerShell). It must match the metadata of the tables in the databases that you are creating. If your new version of the database has altered the tables, then you will need an initial step of doing a single, initial build to an existing version of the database using SCA, using migration scripts where necessary, and then saving the data from that initial build.

The process of reading the data in is relatively straightforward using command-line BCP. Here is a fragment that does it, using a connection string and a SMO database connection. After the BCP session is completed for the databases, all the database constraints are reenabled (they are automatically disabled by BCP to allow the import to succeed whatever order you import the tables).


This only applies if one or more of your targets is on a server that can only be reached via SQL Server credentials, such as a SQL Server in a container, or an Azure connection. Here we simply leave out the password from the connection string. We store the passwords in an encrypted XML file in your user area, using Import-CliXml and Export-CliXml in PowerShell. The downside is that you will be asked for the password to create the password file, if the file isn’t there. The Export-Clixml cmdlet encrypts credential objects by using the Windows Data Protection API. The encryption ensures that the contents of the credential object can be decrypted only by your user account and on only that computer.

The reason that a connection string is so useful is that everything about a connection is stored there and read by the driver, even such things as connection timeouts, and this information is easy to extract once you turn it into a .NET object. Once it is a working script, this should be pretty safe, but it isn’t if you wander off to lunch without locking the Windows session.

This complicates the code and has the disadvantages, and maybe also the advantages, of making the code look more complicated than it is.

The final code

Here is the complete PowerShell code.


It is a curious thought that we often waste time synchronizing development databases to bring them up to the latest version when it is often better, quicker and almost easier to do a clean build and, if you need data, just BCP the data into the empty database.

There comes a point when the amount of data needed precludes this option and a tool like SQL Clone is needed. However, for smaller data volumes, SCA provides a have a nice clean version of the data for development work every time a new version is released.


You may also like

  • Article

    Deploying Data and Schema Together with SQL Compare or SQL Change Automation

    You want to use SQL Compare or SQL Change Automation (SCA) to create or update a database, and at the same time ensure that its data is as you expect. You want to avoid running any additional PowerShell scripting every time you do it, and you want to keep everything in source control, including the data. You just want to keep everything simple. Phil Factor demonstrates how it's done, by generating MERGE scripts from a stored procedure.

  • Event

    IP EXPO Europe 2018

    IP EXPO Europe is Europe’s number ONE ITĀ event for those looking to find out how the latest IT innovations can drive their business forward. Visit Redgate at Stand D27

  • Article

    Transaction-handling techniques in T-SQL deployments

    How are transaction handled when deploying databases with SQL Change Automation? For the most part, we have resisted putting excess structure around the way that changes are deployed to your database. Unlike database projects that use the declarative-style of deployment, which work by synchronizing a source-controlled model of your schema to a target database, we

  • Article

    Allowing for manual checks and changes during database deployments

    SQL Change Automation enables users to make database changes to production safely and efficiently using PowerShell cmdlets, which can be integrated easily into any release management tool. This article will show you how to automate database deployments safely, by using SQL Change Automation from within PowerShell scripts, and how a deployment script for a release can be checked and amended as part of the process.

  • Forums

    SQL Change Automation Forum

    Continuous integration and automated deployments for your SQL Server database