Voice of the DBA

Tag: TDM

The DIY Cost of Masking Test Data For Smaller Organizations

One of the things I’ve tried hard to do in database development situations if ensure I could easily refresh dev and test environments on demand. In a small startup, we wanted to be sure our weekly releases worked well, so if we found bugs in QA, we immediately filed a report with developers and once they viewed a repro, we refreshed QA with a fresh copy from production.

Not the best approach, but for a small database in the early 2000s when we were less concerned about data breaches, this worked well.

At the time I remember discussing this challenge with Andy during one of our SQL Server Central catchups. He had a similar issue, though for him, they needed to clean the data. Their system included a bunch of email notifications and they couldn’t take the chance of sending out test emails. They also were in a regulated industry and clients who were concerned about developers getting names and addresses from production.

Cleaning up names and addresses seems like a simple task, but there were endless variations and a new set of edge cases appearing constantly. It was a regular task for Andy to maintain and adjust his scripts to ensure data was masked well. This also resulted in no shortage of calls from others when things didn’t run smoothly.

A Time Sink

For smaller organizations (50-200 people), it wasn’t, and likely still isn’t, an option to purchase some of the more established tools in this area. They are too expensive and require a lot of resources (time and hardware) to get working.

At the same time, the DIY approach is essentially a commitment to a software development project, one that never ends and distracts people from their regular jobs. If you have staff on salary, this can seem like a good approach, but it’s often a waste of their efforts.

Even in the age of AI, I can see how this would be something that eats up sizable amounts of resources. While AI makes coding easier, directing that coding isn’t easy. And since models only keep limited context, I can see someone spending just as much time directing a model with prompts and correcting its mistakes as they might spend writing the code. I might be wrong, but since this isn’t always an easily defined task, I bet I’d spend a decent amount of time, even with Claude Code, constantly reshaping masking scripts.

Not to mention, I’d still be hoarding the knowledge in my head about how to direct the AI.

A Better Approach

I work for Redgate Software, and certainly I’m a bit biased here. We sell a solution in this area, but I’ve also helped shape (a little) how we approach this space based on the challenges I see from customers, and the needs they have to get a system working quickly. Not to mention an affordable solution that reduces the risk of accidental data loss or regulatory fines.

We’ve developed Test Data Manager to work within the constraints of small to medium sized organizations, both with functionality and price. It has a lot of what I want in a solution, though not everything. I still push on the product and engineering teams to add more features as well as reduce complexity wherever possible.

I want this to be ingeniously simple to use.

I’ve had the opportunity to work with a few customers that have become audit ready in hours by using the smart defaults and adding a bit of their knowledge about the system. The time to value keeps getting lower and I’m impressed by how the team responds to customers requests and demands. Like many of our products, we’re releasing regularly and adding features constantly.

The approach of having a tool that codifies what you want, is easily version controllable, and gets updated regularly is what most people want from software. We try to be good partners, and we’re working to ensure that customers not only get the value for the price they pay, but that value continues to increase throughout the year as we mature the software.

We’re releasing in a DevOps manner, to ensure you can do so for your organization.

If you’d like to see how Test Data Manager can can keep you in control of your databases, reduce your risk of data loss, and help ensure compliance, give us a try .

We also have a webinar (

Compliance Without Compromise: Test Data Management That Finally Fits) coming up on Mar 18 that you might check out for a quick look at some of the benefits of TDM.

March 11, 2026
Adding Manual Relationships Between Tables in the TDM Subsetter
I wrote about getting the Redgate Test Data Manager set up in 10 minutes before, and a follow up post on using your own backup. One of the things I didn’t show from my own database was that it had no FKs, so the subsetting didn’t quite work as I wanted.

A previous post showed how add starting tables for the subsetter to look at, however that didn’t get me a good data set for testing. This post continues looking at the subsetter by adding manual relationships to our configuration.

This is part of a series of posts on TDM. Check out the tag for other posts.

Declaring a Relationship in the Options File

In my previous post, I’d picked a starting table and had reduced the dbo.players table from 16564 to 1800. However, I only had player information. If I query my subset database, I see there is a player, but I have no batting statistics for this player.

This is because my table has no declared FKs in it. If I check the dbo.batting table, I can see only a PK.

Let’s fix this.

Declaring Manual FK Relationships

In the options file documentation, there is a section that notes manual relationships can be declared with a key called “manualRelationships”. If I copy/paste the example section into my options file, I’ll see this:

I don’t have a SourceTest table, so let me edit things. I’ll set a relationship between dbo.players.playerID and dbo.batting.playerID. This gives me the following in my options file.

Before I run my subsetter, here are the row counts by table.

I’ll re-run this subset command, which includes my option file at the end.
```
rgsubset run --database-engine=sqlserver --source-connection-string="server=localhost;database=BB_FullRestore;Trusted_Connection=yes;TrustServerCertificate=yes" --target-connection-string="server=localhost;database=BB_Subset;Trusted_Connection=yes;TrustServerCertificate=yes" --target-database-write-mode Overwrite --options-file E:\Documents\git\TDM-Demos\rgsubset-options-bb.json
```
When I do that, I know see these row counts. Note I now have batting rows.

My player query won’t work, so I still need to declare another relationship with the dbo.teams table. That is shown below:

I can re-run the same command above, and then I see this set of rowcounts (original on left, subset on right).

There is teams data, and if I re-run my queries from the top, I can see stats now.

Now I have a dataset that I can perform development work with in terms of players, teams, and batting.

I can also add more relationships as needed, for example, I’ll add this section to include pitching, batting post, and fielding. Here’s my complete options file:
```
{
  "jsonSchemaVersion": 1,
  "startingTables": [
    { 
      "table":
      {
        "schema": "dbo",
        "name": "players"
      },
      "filterClause": "birthState = 'CA'"
    }
  ],
  "manualRelationships": [
    {
      "sourceTable": 
        { 
          "schema": "dbo", 
          "name": "players"
        },
      "sourceColumns": [ "playerID" ],
      "targetTable": 
        { 
          "schema": "dbo", 
          "name": "batting" 
        },
      "targetColumns": [ "playerID" ]
     },
     {
        "sourceTable": 
          { 
            "schema": "dbo",
 
            "name": "batting"
          },
        "sourceColumns": [ "teamID", "yearID", "lgID" ],
        "targetTable": 
          { 
            "schema": "dbo", 
            "name": "teams" 
          },
        "targetColumns": [ "teamID", "yearID", "lgID" ]
       },
       {
          "sourceTable": 
            { 
              "schema": "dbo", 
              "name": "players"
            },
          "sourceColumns": [ "playerID" ],
          "targetTable": 
            { 
              "schema": "dbo", 
              "name": "battingpost" 
            },
          "targetColumns": [ "playerID"]
         },
         {
            "sourceTable": 
              { 
                "schema": "dbo", 
                "name": "players"
              },
            "sourceColumns": [ "playerID" ],
            "targetTable": 
              { 
                "schema": "dbo", 
                "name": "pitching" 
              },
            "targetColumns": [ "playerID"]
           },
           {
              "sourceTable": 
                { 
                  "schema": "dbo", 
                  "name": "players"
                },
              "sourceColumns": [ "playerID" ],
              "targetTable": 
                { 
                  "schema": "dbo",
 
                  "name": "fielding"
                },
              "targetColumns": [ "playerID"]
             }
  ]
}
```
After re-running the subsetter, I have these row counts. Note there are rows in all the tables defined in the options file.

I can keep adding in more tables as needed to ensure the subsetter can walk down the data relationships I need in my database to produce a useable dev/test dataset that’s smaller than production.

TDM can help your devs build better software and with the subsetter, this can create lots of agility to ensure the data you need to accurately build this software is available.

Give TDM a try today from the repo and a trial, or contact one of our reps and get moving with help from our sales engineers.

Video Walkthrough

Check out a video of my demoing this below:
February 12, 2025
Picking a Starting Table in Test Data Manager
I wrote about getting the Redgate Test Data Manager set up in 10 minutes before, and a follow up post on using your own backup. One of the things I didn’t show from my own database was that it had no FKs, so the subsetting didn’t quite work as I wanted.

This post shows how to correct things and add starting tables for the subsetter to look at in order to customize your setup.

This is part of a series of posts on TDM. Check out the tag for other posts.

The Setup

When I ran the subsetter PoC with my own backup, I showed this screen comparing the size of the dbo.players table before and after.

Here’s what was missing. Let’s look at the counts from all tables. This seems OK, 10%-ish for most tables. The smaller ones are excepted there.

However, if I look at the subset and my LahmanID = 11, I see the player, but no batting stats.

That’s not useful. We got a random 10% from these tables, and my data isn’t intact. In a larger database, I might miss that I had incomplete, unmatched data across tables and write reports or queries that seemed to work, but really didn’t.

Let’s fix this.

Customizing the Subset

We have the ability to customize the way the TDM tools work by adding in things we know, which can’t be detected. Like FKs between tables that aren’t declared. In this case, I want to add in a relationship.

Note: the best place to do this is in a settings file, which I’ll customize for my purposes.

If I look in the TDM-Automasklet repo, there’s a settings file already setup that grabs certain orders for Northwind.

I’ll change this as follows to just grab all players born in CA. I have no idea how many this is, but let’s filter on that.

Let’s start the TDM-AutoMasklet and just run through the subset. To use my settings file, I’ll alter this line (20) in the file:

Since I added my file to another repo, I’ll put in the full path:

I’ll run the PoC and I get to the subset section. I’ll stop, but I’ll copy the subsetter command, which is highlighted below.

This command is this (broken into lines for clarity):
```
rgsubset run --database-engine=sqlserver --source-connection-string="server=localhost;database=BB_FullRestore;Trusted_Connection=yes;TrustServerCertificate=yes" --target-connection-string="server=localhost;database=BB_Subset;Trusted_Connection=yes;TrustServerCertificate=yes" --target-database-write-mode Overwrite
```
However, as of Jan 15, this is missing the options file. I’ll add that to the command, which will now look like this:
```
rgsubset run --database-engine=sqlserver --source-connection-string="server=localhost;database=BB_FullRestore;Trusted_Connection=yes;TrustServerCertificate=yes" --target-connection-string="server=localhost;database=BB_Subset;Trusted_Connection=yes;TrustServerCertificate=yes" --target-database-write-mode Overwrite --options-file E:\Documents\git\TDM-Demos\rgsubset-options-bb.json
```
When I run this

If I now look at rowcounts, I see this in the original (left) and subset (right) databases. Note that there rather than 10% of most tables, I now see 1800 players, but almost no rows in other tables.

This happens as I’ve selected a starting table, a parent, but since I don’t have declared relationships as FKs, the subsetter essentially had no idea where to go. It filtered based no dbo.players.birthState = ‘CA’, but that’s it.

I can add a second starting table, teams, as well. When I do that, I have this in my options file. Note, I’ve filtered on the years after 2000. If I don’t filter, I get all the values passed through. I think this is because this is a fairly small table and I haven’t specified a target size.

When I run this, it’s again quick and I see only 360 rows moved over in the dbo.teams table.

I could add other starting tables if I had different parts of my database that had unreleated entities. However, in this case, most of these tables are related, just not with explicit DRI.

This post has shown a way to start controlling the subsetting. In the next post, I’ll look at adding in the manual relationships.

Give TDM a try today from the repo and a trial, or contact one of our reps and get moving with help from our sales engineers.

Video Walkthrough

Check out a video of my demoing this below:
February 5, 2025
A Quick Test Data Manager Eval with My Database Backup

I wrote about getting the Redgate Test Data Manager set up in 10 minutes before, and it was a great post. In that one, the sample database Northwind was created and used. However, Alex Yates has modified the scripts to work with backup files, and I’ll show you how easy this is in just a few minutes.

This is part of a series of posts on TDM. Check out the tag for other posts.

The Setup

I’ve filtered my SSMS to only show databases with BB in the name. You can see I have none.

I also have a backup file of a baseball database on my d: drive. My local instance has access to this folder as I use for backups and restores in dev/test work.

While I can pass these parameters in, it’s easy to just change the values in the file after cloning the repo. This way it’s easy to see what’s going on.

That’s it, now let’s fire up PowerShell.

Running the Eval

When I run the file, I see it start up and report the various values. You can see that it’s set the base database name to “BB” and I should see the two databases with the suffixes created. I also see my backup path.

This runs and in a few minutes, I see that the databases have been created and we are ready to subset.

Checking SSMS with a refresh, I see the databases.

If I type “y”, the subsetter runs, and very quickly. This isn’t a massive database, but it is thousands of rows, which makes it easy to play with.

If I run counts, I see this. The left is the full restore, which has 16k records. The subset, on the right, has about 10% of that, with 1644 rows. Pretty cool. So far, this has taken less than a couple of minutes.

Now let’s continue to press “y” and get the classification, mapping, and masking done. Two tables were found with PII (names) and masked.

If I query the tables, I see the results below. Notice that not all values were moved, as the first ID in the subset is 11, but we can see IDs 11 and 22 were masked.

This was a very quick look at running an eval with my own database backup, not a sample db. We’ve had a few people ask to do this for their own testing, and we modified the scripts to work with backups.

Give TDM a try today from the repo and a trial, or contact one of our reps and get moving with help from our sales engineers.

Video Walkthrough

Check out a video of my demoing this below:

January 29, 2025