Benchdog SQL Data Scrambler

Click a ticked table to open its Columns tab. Each column shows its rule and the reason, worked out from its name: names, emails, phones and street addresses are faked, card, account and ID numbers are masked, dates of birth are moved by up to a year, pay moves a little, and everything else is kept.

Choose another rule from those that fit the column: Keep, Fake with the generator to use, Shuffle, Mask with how many characters stay at the end, Set to NULL, One value, Noise with how far a number moves, or Date shift with how many days at most. Automatic goes back to the rule from the column's name, and Set every column back to automatic clears a table's choices. A free text column such as Notes is kept unless you choose a rule for it.

Tables you have not ticked whose columns look personal are tagged amber, and a line above the tables says how many there are. Tick it, or Tick them, ticks them all. On the command line, --suggested does the same.

Plan a scramble with benchdog data scramble --server localhost --database Shop_Dev --windows --suggested --columns. It ticks every table that looks personal, shows how each column would change and why, and only reads. Use --table dbo.Person for one table, and --column dbo.Person.Salary=noise:5 to choose a rule, with keep, fake:email, shuffle, mask:4, null, fixed:Hidden, noise:5 or dateShift:90 after the equals sign.

Add --preview dbo.Person to see the first 20 rows before and after, or --preview-rows 50 for more. Add --scramble to change the copy now: the command shows This changes rows in Shop_Dev on localhost. Type the database name to confirm: and changes nothing until you type it. Add --backup to take a copy-only backup first on SQL Server, --seed 42 when two runs must match, --locale de for the language of fake values, and --yes to skip the typed confirmation in scripts.

For Azure SQL, leave out --windows and sign in as you. For a server that only takes SQL logins, add --sql-login sa; Benchdog asks for the password without showing it.

You do not need to do anything. Primary keys, foreign keys, identities and the columns another table's foreign key points to are kept. The same value gets the same result in every table, ignoring case and spaces at the end, so an email scrambled in one table still matches the same email in another. In a unique column, two different values never get the same result.

Set a seed in the scramble's settings, or pass --seed 42. The same seed turns the same value into the same result each run. Leave the seed empty when you do not need that: every run then uses a new one, so nobody can check a guess against the scrambled copy, and the preview notes that the scramble will not match it exactly.

Right-click a scramble in VS Code and choose Save as Project File..., or in the desktop app choose File, Save as project..., or pass --save-project shop.datascrambler.json on the command line. The .datascrambler.json file keeps the database, tables, chosen rules, seed, language, rows at a time and whether triggers fire, and never a password or a value from the database.

Run it with benchdog data scramble --project shop.datascrambler.json --scramble --backup, open it in VS Code for IntelliSense, problems and an Open Scramble link, or in the desktop app use Change database... to point the same scramble at another copy and keep its tables, rules and settings.

Pick the language in the scramble's settings, or pass --locale with a code such as en_GB, de or fr. Fake names, addresses and phone numbers can be made in 18 languages and regions, including English (UK), English (US), German, French, Spanish, Dutch, Polish, Japanese and Chinese (Simplified).

No. SQL Data Scrambler is made for test and dev copies and changes a database in place, so the real values are gone once a table is scrambled. Take a fresh copy of the live database, point the scrambler at the copy, and keep the live database out of its list. Nothing changes until you choose Scramble, read what it changes and type the database's name.

Opening a scramble reads the tables, columns, keys and row counts. Previewing and scrambling read only the key and the columns that change, in batches of 5,000 rows unless you choose otherwise. Each table is scrambled in its own transaction: each batch's new values go to a temporary table, the table changes from it in one update at the end, and the table is changed whole or left as it was. Triggers on the table are turned off while it changes, unless Let triggers fire is on.

On SQL Server, Back up the database first takes a copy-only backup in the server's default backup folder, and nothing is scrambled if it fails. Azure SQL keeps its own point-in-time backups, so the option is not offered there.

Primary and foreign keys, identities, computed, rowversion, generated always and Always Encrypted columns are kept, so rows still join. External, FileTable, memory-optimized and history tables are left out and tagged red with the reason. A table with no primary key can only be changed with Set to NULL or One value. xml and sql_variant columns, and types such as geography, can only be set to NULL or one value.

A system-versioned table is scrambled, but its history table still holds the real values, and the plan says so. Columns whose names do not look personal are kept, so check the Columns tab for free text that may hold personal data and choose a rule for it.

No. A saved scramble and a project file keep the server, database name, how to sign in, tables and chosen rules, never a password and never a value from the database. With the seed left empty, every run uses a new one, so nobody can check a guess against the scrambled copy.

Yes. Planning only reads, and Preview Rows shows the first 50 rows of a table before and after with every changed value marked, without writing anything. The Scramble window then says what changes and waits until you type the database's name.

Yes. Every tool has a thirty day trial with every feature, and no card. Ask for one at benchdog.dev/trial with your name and email, and the trial licence arrives by email, as a line to paste and a benchdog.licence file. One trial per person covers every tool at once.

One licence is for one person and covers the VS Code extension, the desktop app and the benchdog command, on every machine that person works from. There is no machine limit and no server count. Buying another tool reissues your licence with that tool added.

The licence shows whose it is: your name and email appear in each tool's About and account menu.

The last version released while you were paid keeps working. Only updates stop. A licence is checked offline and never expires a version it covers, so nothing stops working the day your year ends.

The VS Code extensions run in VS Code 1.95 or later on Windows, Linux and Mac. The desktop apps and the benchdog command are single programs for Windows (x64), Mac (Apple silicon) and Linux (x64), with no .NET install needed. The SQL tools work with SQL Server 2017 or later, Azure SQL Database and Azure SQL Managed Instance.

No. The engine runs on your machine, reads your vaults and databases as you, and keeps the results with you. A tool contacts benchdog.dev only when you click something that needs it, such as asking for your licence, and never in the background. Licences are checked offline.

Every change a tool makes runs in one transaction with XACT_ABORT on, so it all happens or none of it does. A SQL Schema Compare deploy, a SQL Data Comparer sync and a SQL Data Filler fill are each one transaction, and SQL Data Scrambler scrambles each table in one. The scripts the tools write for you to run in SSMS or sqlcmd are wrapped the same way and roll back if any batch fails. SQL Multi Runner runs your own script as it is, and wraps each database in a transaction when you tick that. Before any change, a tool says what it will do and asks first: SQL Schema Compare names the tables and columns whose rows go, SQL Data Comparer says how many rows change, and SQL Data Scrambler asks you to type the database's name. SQL Schema Compare also stops if either database changed after you read the plan. Even so, nothing replaces a backup, and the SQL Server tools can take one first.

Azure calls run as you, through a browser sign-in once or through your existing az login, in any tenant you belong to. The SQL tools use your Microsoft account, your Windows sign-in or a SQL login, and every connection is encrypted. A SQL login's password is kept until the app closes, or in your system's secure store (DPAPI on Windows, Keychain on Mac, libsecret on Linux) if you tick Remember. It is never written to a settings file, a project file or a log.

The message Nothing changes. Tick the tables to scramble, and choose how their columns change. means no ticked table has a column that would change, so the scramble is refused before anything is asked. Tick a table that looks personal, or open a ticked table's Columns tab and choose a rule for the columns to change. A table whose columns are all kept says Nothing in dbo.Visit changes. in its preview.

The message dbo.Person.Email is unique, so Fixed would give every row the same value. Use Fake, Mask or Null. means One value was chosen on a column with a unique constraint or index, which SQL Server would refuse. Choose Fake, Mask or Set to NULL instead.

The message dbo.Contact.Name: Fake changes each row on its own, which needs a primary key, and dbo.Contact has none. Use Null or Fixed. means the table has no primary key, so its rows can not be told apart to be changed one by one. Only Set to NULL and One value can change such a table; other rules are kept with a note. Add a primary key to the copy if the column must be faked.

The message Scrambling dbo.Person failed: ... dbo.Person was left as it was. 1 table was scrambled before it: dbo.Visit. names the table and lists the tables already done, because each table runs in its own transaction. The rest of the message says why, without showing any duplicate value. dbo.Person.Email is unique, and Mask ran out of different values for it. Choose Fake or another rule. means a short masked value could not be made unique; choose Fake for that column. Fix the cause, untick the tables already done, and scramble again.

Without a seed every run uses a new one, and the preview says With no seed, every run makes other values, so a scramble will not match these exactly. Set a seed in the settings or with --seed when the preview and the scramble must match.

The note dbo.Person keeps its history in dbo.PersonHistory, which still holds the real values. Clear or drop the history too. means the table is system-versioned. SQL Server keeps the history table itself, so the scrambler leaves it out. Clear or drop the history on the copy after the scramble.

Turn on the diagnostics log: in a desktop app, Settings, then Write a diagnostics log; in VS Code, the extension's diagnostics setting; for the benchdog command, set the environment variable BENCHDOG_DIAGNOSTICS=1. Each run writes one file to the logs folder in the Benchdog folder (%LOCALAPPDATA%\Benchdog\logs on Windows, ~/Library/Application Support/Benchdog/logs on Mac, ~/.local/share/Benchdog/logs on Linux), and the last ten are kept. The desktop apps have Open logs folder, and VS Code has the Open Diagnostics Logs Folder command.

The log records sign-in requests, Azure calls, engine requests, SQL connections, times and errors. Passwords, connection strings, tokens, secret values and your data are never written, so it is safe to send to support.

That is the crash window, for an error the app did not expect. Copy details puts the error and its details on the clipboard, Email support opens a message to support with them, and Open logs folder shows the crash report, which is written even with the diagnostics log off. If the error happened on a background thread the app has to close, and the window shows when it next starts.

Browser sign-in keeps its tokens in the desktop keyring, and that machine has none unlocked, which is common in WSL. Install and unlock a keyring such as GNOME Keyring or KWallet, or sign in with az login and turn on Use az login in Settings or pass --cli to the benchdog command. Benchdog never falls back to a plain file for tokens.

The SQL Server you connected to uses a certificate your machine does not trust, which is usual for a server on your own network with a self-signed certificate. Pick Trust the server's certificate in the picker, or add --trust-server-certificate on the command line, for servers you know. For a server you do not know, install its certificate chain instead.

In a desktop app, choose Forget saved password from the menu. On the command line, run benchdog sql forget-password --server <server> --sql-login <login>. Either removes it from the system's secure store, which is the only place it was kept.

Write to support@benchdog.dev or use the support form on this site, and a person replies. Say which tool and version you were using (Help, About in a desktop app shows both), what you did and what you saw, and attach the diagnostics log if you have one.