Benchdog SQL Schema Compare

Run benchdog schema compare --source-server localhost --source-database Sales --target-database Sales_Copy --windows. It lists one line per object that differs or is on one side only, then what differs in each, such as a column's type or a procedure's code, then a summary such as 3 different, 1 only in the source, 1 only in the target, 120 the same. Add --all to list the objects that are the same too, and --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. Comparing reads only the structure, never table data, and changes nothing.

Choose Options... in VS Code or the desktop app and tick what does not count. There are fourteen options. On by default: spaces and line breaks in code, the order of a table's columns, names SQL Server made up for keys and constraints, filegroups, and the server and database a synonym points to. Off by default: comments in code, upper and lower case, square brackets, column collations, identity start and step, descriptions and extended properties, permissions, role memberships and data masking.

On the command line, --ignore comments --ignore permissions turns options on and --count column-order turns one off; the keys are whitespace, comments, case, brackets, column-order, collations, system-names, identity-seed, filegroups, extended-properties, permissions, role-members, synonym-database and masking. The options you change are saved with the comparison, and Defaults puts them back.

Click the object. The details say what differs in plain words, such as Column Email: only in the source: nvarchar(200) NULL, show when the object last changed on each side where SQL Server records it, and show its CREATE script on each side with the lines that differ marked: green in the source for what deploying adds, red in the target for what it takes away.

Code is read as T-SQL, so a -- inside a string is never taken for a comment. Encrypted code cannot be compared, and its script reads -- The code is encrypted, so SQL Server does not show it.

Run benchdog schema compare --project sales.schemacompare.json --fail-on-difference. The exit code is 3 when anything differs, 0 when the databases match for what the project compares, and 1 when the command itself fails, such as when a database cannot be reached. Add --script release.sql to the same run to keep the deploy script as a build artefact.

Sign in with --windows on a Windows agent, or with --sql-login, whose password the command reads from the first line of piped input when nothing is typed, so no password sits in the pipeline's command line.

Show Deploy 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 Deploy Script writes a T-SQL file to run in SSMS, sqlcmd or Azure Data Studio. On the command line, benchdog schema compare --project sales.schemacompare.json --script deploy.sql writes it; add --drop to also drop what is only in the target, and --object dbo.Customer to deploy only that object.

The warnings sit at the top of the script under Read first. It runs in one transaction with XACT_ABORT on, and once anything fails the rest is skipped and it rolls back, even in sqlcmd or SSMS.

In VS Code, Deploy works out the deploy from both databases as they are now, says what it changes and what to read first, and deploys after you confirm; on SQL Server, Back Up and Deploy takes a copy-only backup first. In the desktop app, Deploy... opens the Deploy window with Back up the target first, Compare again after the deploy and Save a copy of the deploy script. On the command line, benchdog schema compare --project sales.schemacompare.json --deploy asks you to type the target database's name first; --yes skips the prompt for scripts, and --backup and --keep-script deploy-ran.sql go with it.

Drops run first, from the most dependent, then creates and changes from the least dependent, then permissions and extended properties, all in one transaction. The tab compares again afterwards.

Do nothing: they are left alone unless you choose them. In VS Code and the desktop app, what differs and what is only in the source start ticked, and what is only in the target starts clear, because deploying it drops it. On the command line, objects in the target only are left unless you add --drop.

When you do choose one, the warnings say so before anything changes, such as Table dbo.OldReport is dropped.

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.schemacompare.json on the command line. A .schemacompare.json file keeps the source, target, the options you changed from their defaults and the names to keep or leave out, 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 schema compare --project sales.schemacompare.json. With --project, --ignore and --count change the file's options, and --include and --exclude replace its lists.

Comparing reads only the catalog, never table data, so the account needs only to see the objects and their definitions. The deploy creates, alters and drops objects in the target and grants and revokes permissions when those are deployed, so the account needs the rights to do that; db_owner on the target covers it. Backing up the target first needs the BACKUP DATABASE permission.

Schemas, users, roles, types, sequences, tables, views, functions, procedures, synonyms and triggers. Objects pair by kind and name, ignoring case unless either database's collation minds case. Columns, keys, indexes and constraints pair by name, and ones with names SQL Server made up pair by shape.

For each object it compares the code, settings, descriptions and extended properties, permissions and role memberships. Permissions on an object go with the object, on a schema with the schema, and on the database with the user or role they are granted to. How the dbo user signs in is not compared.

In place, without rebuilding. Code that differs uses ALTER, so it keeps its permissions. Tables change with ALTER TABLE: columns are added, dropped or altered, and keys, indexes and constraints that differ, or that use a column that changes, are dropped and added back. Users, roles, schemas, sequences, synonyms and triggers change in place too.

Other tables' foreign keys that point to a key that changes, and code schema bound to a table whose columns change, are dropped and added back, and the warnings say so. What a deploy cannot change in place it leaves, with a warning: an identity column's identity, column order, a table's filegroup, memory optimising or system versioning, a sequence's type and start, a type a column uses, and how a user signs in. Memory-optimised, external and FileTable tables are not created, and encrypted and .NET code cannot be made.

Only when you choose something that drops it, and it tells you first. Dropping a table or column is warned about, such as Table dbo.OldReport is dropped with its 1,200 rows. or column Fax is dropped with its data., and objects only in the target start clear so nothing is dropped unless you tick it. A column that changes type warns that if a value does not fit, the deploy stops and nothing changes, and a column that becomes NOT NULL warns the same about NULL.

The whole deploy is one transaction, so a failure rolls it all back. Both databases are read again just before it runs, and if either changed since you read the plan, so that the deploy would no longer be the one you confirmed, it stops and nothing changes: compare again and deploy from the new plan. On SQL Server you can back the target up first, as a copy-only backup that leaves your own backup chain as it is.

It compares and deploys structure, not rows; Benchdog SQL Data Comparer compares and syncs data. It does not rebuild a table to change its column order, its identity or its filegroup, and it does not create memory-optimised, external or FileTable tables. The same database on both sides is refused.

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.

As it was. The message starts The deploy failed at with the step's name, then and was rolled back, so the target is as it was: and what SQL Server said, with any row values left out. The whole deploy is one transaction. The most common causes are a value that no longer fits a column that changed type, and a NULL in a column that became NOT NULL, both of which the Read first warnings named beforehand. Fix the data or the change, then deploy again.

It has found something the script cannot get past, and tells you before anything runs. column Email is added as NOT NULL with no default, but the table has 1,200 rows, so the deploy will stop. Give it a default first. means the source added a required column that the target's rows have no value for. Schema archive still holds objects that are not dropped, so dropping it will stop the deploy. means a schema was ticked to drop while objects in it were not. Change the source, or tick or clear the objects named, and compare again.

The view, function, procedure or trigger was created WITH ENCRYPTION, so SQL Server does not give its code to anyone, and it cannot be compared. A deploy leaves it alone and says so: Procedure dbo.GetOrders is left as it is, since its code is encrypted or written in .NET. Deploy it from its source code instead.

Azure SQL keeps its own point-in-time backups, so Benchdog does not back it up: VS Code shows Deploy without Back Up and Deploy, and the desktop app does not offer Back up the target first. 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 is deployed.

A description or some code holds a line that is just GO, which sqlcmd and SSMS would read as the end of a batch, so a script file would be split in the wrong place. The message names it and ends Deploy straight to the target instead. Deploy from VS Code, the desktop app or --deploy, which runs the same steps without a file.

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.