Benchdog SQL Data Filler

Click a ticked table to open its Columns tab. Every column shows what fills it and why, matched from its name and type, so Email gets emails, City gets cities and UnitPrice gets amounts of money. Identity columns are numbered on, foreign keys take values from the table they point to, and columns SQL Server sets are left to it.

For any other column, pick a generator from more than 50 in the groups Person, Business, Address, Internet, Text, Number, Date and time, Other and Your own values. Its settings show beside it: a smallest and largest number, an earliest and latest date, a list of values, a pattern such as [A-Z]{3}-[0-9]{4}, a SQL query or a CSV file and its column. Empty % sets how many rows in a hundred are left NULL. Choosing Automatic goes back to the matched generator, and Set every column back to automatic clears the table's choices.

Choose From a list and type the values, one per line, or choose a pattern written like a regular expression, such as (GB|IE)[0-9]{2}. A SQL query runs against the database being filled and the column picks from its first column, so a column can take values from another table. A CSV file is picked by its column, by name or number, and is found from the project file's folder when the fill is saved as a project.

A choice that can not work stops the fill with the reason, such as dbo.Product.Price: Email can not go in a decimal column. or dbo.Customer.City: a list needs at least one value.

Set the seed in the fill's settings, or pass --seed 42 on the command line. Every random choice starts from the seed, so the same seed makes the same rows on every computer. Each column has its own seeded generator, so adding a column leaves the values in the other columns as they were.

Plan a fill with benchdog data fill --server localhost --database Shop --windows --table dbo.Customer=500 --table dbo.Order=2000 --columns. It lists the tables in fill order, parents first, with how each column is filled, and only reads. Use --all --rows 200 to fill every table, and --locale en_GB, --empty-first, --fire-triggers and --no-check-constraints to choose how.

Add --preview dbo.Customer to see the first 20 rows the table would get, or --preview-rows 50 for more. Add --script shop-fill.sql to write the fill as a script. Add --fill to add the rows now: the command shows This changes rows in Shop 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, 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.

Choose Save Fill Script in the Fill tab, press Ctrl+S in the desktop app, or pass --script shop-fill.sql on the command line. The script holds the same rows a fill adds, as INSERT statements of up to 1,000 rows, and runs in one transaction, so a failure changes nothing. Review it and run it in SSMS, sqlcmd or Azure Data Studio.

Turn on Empty tables first in the fill's settings, or pass --empty-first. The tables' rows are deleted, children first, in the same transaction as the fill, and identity columns start again from the table's identity seed.

It is refused while a table you are not filling points to one of them and keeps its rows. The plan says dbo.Customer can not be emptied first, as dbo.Order points to it and keeps its rows. Fill dbo.Order too, or add rows without emptying.

Pick the language in the fill's settings, or pass --locale with a code such as en_GB, de or fr. Names, addresses and phone numbers can be made in 18 languages and regions, from English (UK) and English (US) to German, French, Spanish, Dutch, Polish, Japanese and Chinese (Simplified). A person's name, email and address in the same row belong together, so the email matches the name beside it.

Right-click a fill in VS Code and choose Save as Project File..., or in the desktop app choose File, Save as project..., or pass --save-project shop.datafiller.json on the command line. The .datafiller.json file keeps the database, tables, rows, column settings, seed, language and fill options, and never a password.

Open it in VS Code for IntelliSense, problems in the Problems panel and an Open Fill link at the top, or run it with benchdog data fill --project shop.datafiller.json --fill. Comments and trailing commas are allowed in the file.

Opening a fill reads only the database's tables, columns, keys and row counts, never table data. Planning, previewing and saving a script only read. The database changes only after you choose Fill and confirm what it adds, and then every row goes in within one transaction, so they all go in or none do.

The fill uses bulk copy and numbers identity columns itself, which needs ALTER permission on those tables. Emptying tables first deletes their rows in the same transaction.

Yes. Parents are filled before children, so every foreign key points to a real row, including rows made earlier in the same fill. Primary keys, unique constraints and unique indexes never repeat and are never NULL. Simple check constraints are read and kept: bounds such as a price above zero, lists of allowed values, and two columns in order, which are swapped when they come out the wrong way round.

Computed, rowversion and generated always columns are left to SQL Server. Tables that point to each other leave one nullable key empty, or are left out with the reason. A check constraint it can not read is noted, as some rows may be refused.

No. The rows are made up, not copied from real people, unless you point a column at your own list, query or CSV file. Names, emails, addresses, companies, products, amounts, dates and text are all generated, and the same seed makes the same rows every time.

On SQL Server, yes. Choose Back Up and Fill in VS Code, tick Back up the database first in the desktop app, or pass --backup on the command line. It takes a copy-only backup in the server's default backup folder, and nothing is filled if the backup fails. Azure SQL keeps its own point-in-time backups, so the option is not offered there.

Columns of types it can not make, such as geography, geometry, hierarchyid and Always Encrypted columns, are left NULL or to their default; when neither is possible the table is left out with the reason. Identity, computed and rowversion columns can not be chosen, and a foreign key column can only choose its share of NULLs.

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 Filling dbo.Customer failed: ... Data Filler numbers identity columns itself, which needs ALTER permission on the table. Nothing was changed. means your login can insert rows but can not keep the identity values Data Filler made. Ask for ALTER permission on the tables with identity columns, or run the fill as a login that has it. The transaction rolled back, so nothing changed.

Hover over the tag to read why. It and dbo.Order point to each other through keys that can not be empty. means two tables point to each other and neither key is nullable, so neither can go first. It needs rows in dbo.Customer, which is empty. Fill dbo.Customer too. means a parent has no rows to point at; tick the parent as well. External tables, FileTables, graph edge tables and history tables are left out too.

An amber tag with a note says why. When a unique key's values run out, clashing rows are made again, text gets a number when it keeps clashing, and when there are no more values to use the table gets fewer rows. Widen the column's range, give it a bigger list, or ask for fewer rows.

The plan notes Data Filler can not read the check constraint CK_Order_Total, so some rows may be refused. Set its columns' values to suit it, or turn off checking. Only simple constraints are read: bounds, lists of values and two columns in order. Set the columns' generators or ranges to fit the constraint yourself, or turn off Check constraints, in which case SQL Server marks the constraint not trusted.

The message Data Filler can not make names and addresses in xx. Use a language such as en, en_GB, de or fr. means the locale code is not one of the 18 the tool knows. Pick a language from the list in the fill's settings, or pass one of the codes shown with --locale.

The message Shop is on Azure SQL, which keeps its own point-in-time backups, so Benchdog does not back it up. means the database is on Azure SQL, where the server takes backups itself. Restore to a point in time from the Azure portal if you need to undo a fill. The backup option is only shown for SQL Server.

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.