Benchdog SQL Data Comparer

Run benchdog data compare --source-server localhost --source-database Sales --target-database Sales_Copy --windows. It lists each table with how many rows are the same, different, only in the source and only in the target, then the tables left out and why. Comparing only reads.

Add --rows 10 to see up to ten differing rows of each table, with the values that differ. Add --target-server when the target is on another server. Leave out --windows to sign in to Azure SQL with Entra, or use --sql-login sa for a SQL login, with --target-sql-login when the target needs a different one.

Choose its key columns. In VS Code and the desktop app the table is listed as left out, with Choose Key beside it, so pick the columns that identify one row. On the command line give --key dbo.Tag=Code, or --key dbo.Tag=Code,Region for more than one column.

Rows match by the key you choose, else the primary key, else a unique constraint or index. The key is saved with the comparison, so it is used every time.

Keep or leave out tables by name, with * and ? as wildcards: --include "dbo.*" and --exclude "dbo._*" on the command line, or tick the tables in the desktop app's Comparison window. In VS Code and the desktop app, Filter... compares only the rows of a table that meet a SQL condition, on both sides; on the command line that is --where "dbo.Order=Placed >= '2024-01-01'".

A filter must be one condition, such as IsActive = 1. Filters and table lists are saved with the comparison and written to its project file.

Show Sync Script opens the script for what is ticked in a new, unsaved SQL editor in VS Code, or a window in the desktop app, with Copy and Save as... Save Sync Script writes a T-SQL file to run later in SSMS, sqlcmd or Azure Data Studio. On the command line, benchdog data compare --project sales.datacompare.json --script sync.sql writes it.

The script runs in one transaction with XACT_ABORT on, and every batch is guarded, so a failure anywhere leaves the target as it was. It has a banner per table and each clause on its own line. A very long script shows its first 1,000,000 characters; saving writes all of it.

Above each table's grid, the Sync switches turn adding rows only in the source, updating rows that differ, and deleting rows only in the target on or off for that table. Clear a row's tick to leave that row as it is. The tick in the table list's header ticks or clears every table that can be synced, and the tick in the grid's Sync header every row on the page.

The script and the sync follow your choices and say how many rows they left as they are. Your ticks stay when the tab compares again.

In VS Code, Sync Target says how many rows in how many tables change, in one transaction, and whether the target's triggers fire. In the desktop app, Sync target... opens the Sync window, which offers Back up the target first, Compare again after the sync, Let the target's triggers fire and Save a copy of the sync script. On the command line, benchdog data compare --project sales.datacompare.json --run asks you to type the target database's name first; --yes skips the prompt for scripts, and --backup, --keep-script sync-ran.sql and --fire-triggers go with it.

A sync deletes, then updates, then adds rows, table by table, so a unique value freed by one row can be taken by another. Each table is compared again as the sync is made, so it matches the databases as they are then. Afterwards the comparison runs again.

Save it as a project file: right-click the comparison in VS Code and choose Save as Project File..., use File, Save as project... in the desktop app, or add --save-project sales.datacompare.json on the command line. A .datacompare.json file keeps the source, target, tables, keys and filters, and never a password. Comments and trailing commas are allowed.

Open the file in VS Code for IntelliSense, its problems in the Problems panel and a Compare link at the top. Run it with benchdog data compare --project sales.datacompare.json. With --project, --include and --exclude replace the file's lists, and --key and --where are added to its own, table by table.

In the desktop app, tick Back up the target first in the Sync window. On the command line, add --backup to --run. The backup is copy-only, so your own backup chain is not affected, and it goes to the server's default backup folder, named after the database and the time. If the backup fails, nothing syncs.

Backing up is only for SQL Server. Azure SQL keeps its own point-in-time backups, so the option is not offered there. In VS Code, Sync Target has no backup option; use the desktop app or the command line when you want one.

Comparing only reads: the account needs to read the catalog and select from the tables it compares. To see the real values of masked columns it needs the UNMASK permission; without it, a note says differences may be hidden.

A sync deletes, updates and inserts rows in the ticked tables. It also turns the target's foreign keys and triggers on those tables off and on with ALTER TABLE and DISABLE TRIGGER, and uses SET IDENTITY_INSERT for identity columns, so the account needs ALTER on those tables as well as INSERT, UPDATE and DELETE. Backing up the target first needs the BACKUP DATABASE permission.

Tables pair by schema and name, and columns by name, ignoring case. Columns of the same type family compare, so int compares with bigint but not with nvarchar. Rows match by the key you choose, else the primary key, else a unique constraint or index.

Values compare exactly: case and accents count, and only what SQL Server ignores is ignored, which is trailing spaces in text and trailing zeros in binary. Each side is read once in key order and merged as it streams, so a big table needs little memory. A repeated key or rows out of order stop that table with an error rather than give a wrong answer, and the other tables carry on.

Computed, rowversion, generated always and Always Encrypted columns are left out, each with the reason. External tables, FileTables, graph tables, the history tables of temporal tables, and tables or columns on one side only are left out too, with the reason. A table with no key is left out until you choose its key columns.

Notes say when masked columns could hide differences, and when the target has a column that added rows need a value for. If any table cannot be compared, no script is written and nothing runs.

No. Only the ticked tables and rows change, and updates set only the columns that differ. The target's foreign keys on those tables are turned off while they change and back on after, checking every row; ones SQL Server did not trust go back as they were. Triggers on those tables are turned off while they change, unless you let them fire.

It all happens in one transaction, so it all happens or none of it does. The same database on both sides is refused, and the target changes only after you confirm.

It compares and syncs rows, not structure: a column or table that exists on one side only is listed as left out, not created. Benchdog SQL Schema Compare does that. Graph tables are not compared yet, and Always Encrypted columns cannot be read.

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.

Rows need a key to be matched. Choose Key beside the table in VS Code or the desktop app, or give --key dbo.Tag=Code on the command line, picking columns that identify one row. A filtered or disabled index is never used as a key, nor an xml, sql_variant or spatial column, so a table whose only unique index is one of those also needs a chosen key.

The key chosen for that table, or the one found for it, has the same values on two rows, so the table stops with that error rather than give a wrong answer; the other tables carry on. Choose key columns that are unique on both sides. Rows in the source came back out of key order, so they can not be matched safely. is the same kind of stop, and comparing again or choosing another key clears it.

No. The message starts The sync failed and was rolled back, so the target is as it was: and ends with what SQL Server said, with any row values left out. The whole sync is one transaction. A common cause is a target column that needs a value the comparison does not give, which the notes warn about before you sync. Fix the cause and sync again.

Azure SQL keeps its own point-in-time backups, so Benchdog does not back it up, and the option is not shown. On the command line, --backup against Azure SQL stops with Sales_Copy is on Azure SQL, which keeps its own point-in-time backups, so Benchdog does not back it up. On SQL Server, the account needs the BACKUP DATABASE permission, and if the backup fails nothing syncs.

A filter must be one condition. The message reads The filter for dbo.Order can not be used: followed by the problem and Give one condition, such as Created >= '2024-01-01'. A ;, a comment, or a ) that closes a bracket it never opened is refused, so a project file from someone else cannot run another statement while a compare only reads.

The source and target must be two different databases. Pick another database, or the same name on another server. A project file with the same database on both sides is refused too, with the source and target are both Sales on localhost. Use two different databases.

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.