sqlserver-import-export

README

sqlserver-import-export

A CLI that exports a SQL Server database's schema and data to two plain SQL files, and imports them back into another SQL Server instance. Handles self-referencing and circular foreign keys, real production data volumes (tested against tables in the tens of millions of rows), and doesn't choke the way SSMS's "Generate Scripts" tends to on either of those.

It's a lot less fiddling than bouncing between SSMS's export wizard, bcp, and hand-written scripts to get a real database with messy foreign keys from one server to another — export, copy two files, import, done:

schema.sql        CREATE TABLE / INDEX / FOREIGN KEY statements
data-import.sql   Batched INSERT statements, wrapped in one transaction

Nothing proprietary — both are ordinary T-SQL you can open, diff, hand-edit, or run yourself with sqlcmd. Import mode exists mainly because feeding gigabytes of generated SQL through sqlcmd by hand is tedious and doesn't retry sensibly when one table has a bad row.

Installation

Requires Go 1.24+.

go install github.com/rafaqwe1/sqlserver-import-export@latest
# or pin a specific version:
go install github.com/rafaqwe1/sqlserver-import-export@v0.1.1

Or grab a prebuilt binary (Linux/macOS/Windows, amd64/arm64) from the releases page — no Go toolchain needed.

Or from source:

git clone https://github.com/rafaqwe1/sqlserver-import-export.git
cd sqlserver-import-export
go build -o sqlserver-import-export .

sqlserver-import-export -version prints the installed version.

Quick start

./sqlserver-import-export -mode export \
  -conn "sqlserver://user:pass@source-host:1433?database=MyDb" \
  -dir ./backup

./sqlserver-import-export -mode import \
  -conn "sqlserver://user:pass@target-host:1433?database=MyDb" \
  -dir ./backup

./backup/schema.sql and ./backup/data-import.sql are just files — look at them before you trust them.

Both modes print how long they took when they finish (export completed in 1m32s), and how long they'd been running if they fail instead.

Usage

sqlserver-import-export -mode <export|import> -conn <connection-string> [flags]

Flag	Modes	Default	Meaning
-mode	both	(required)	export or import
-conn	both	(required)	Connection string — source DB for export, target DB for import
-dir	both	.	Output directory (export) or input directory (import)
-tables	export	(data for all tables)	Comma-separated table names to export data for, or a path to a file listing one per line — schema.sql always includes every table regardless
-ignore-tables	export	(none)	Comma-separated table names to exclude data for, or a path to a file listing one per line — applied after -tables. The table is still created, empty, in schema.sql
-batch	export	1000	Rows per INSERT statement (SQL Server's hard limit is 1000; higher values get clamped down with a warning)
-parallel	export	min(NumCPU, 8)	Number of tables read from the source concurrently
-skip-schema	import	false	Skip schema.sql, only run data-import.sql (target tables already exist)
-reset	import	false	Clear the target first, so re-running import doesn't fail on leftovers from a previous run

-reset

Running import twice against the same target fails the second time — schema.sql tries to CREATE TABLE objects that already exist. I kept hitting this while iterating, so -reset clears the target first:

• -reset alone: drops every FK and table in the target, then proceeds normally — schema.sql recreates everything, data-import.sql reloads it.

• -reset -skip-schema: leaves table structure alone and just empties every table (constraints disabled, rows deleted, constraints re-enabled) before reloading. This is the one I actually use most, for repeatedly reloading data during testing without recreating the schema each time:

  ./sqlserver-import-export -mode import -conn "..." -dir ./backup -skip-schema -reset
  

No confirmation prompt — the flag itself is the confirmation. Don't script it against a database you don't mean to wipe.

Selecting which tables' data to export

-tables and -ignore-tables only control data — schema.sql always has every table, with every foreign key, regardless of either flag. This is deliberate: it means an imported target always has the full table structure, so an application pointed at it doesn't break over a missing table, and it means a foreign key is never silently dropped.

Both flags take either an inline comma-separated list:

./sqlserver-import-export -mode export -conn "..." -dir ./backup \
  -tables "dbo.Customers, Orders, sales.Invoices"

or a file, one table per line (-- for comments, blank lines ignored):

# tables.txt
dbo.Customers
Orders
-- Invoices is exported separately, skip it here

A name without a schema prefix defaults to dbo. -ignore-tables is applied after -tables, and is usually the more convenient of the two — e.g. to export everything except a handful of noisy log tables, which still get created (empty) on the target instead of vanishing:

./sqlserver-import-export -mode export -conn "..." -dir ./backup \
  -ignore-tables "dbo.infra_log, dbo.infra_auditoria"

Naming a table that doesn't exist in the source database is an error for either flag, rather than a silent no-op.

One consequence of leaving a table's data out: if some other, included table has a NOT NULL foreign key pointing at it, that row now references a table with nothing in it. The insert fails its FK check during import — loudly, the same as any other bad row — rather than being let through. If you hit that, either include the referenced table's data too, or don't ignore it.

Connection strings

Both forms work — either is handled by the driver:

sqlserver://user:password@host:1433?database=MyDb
server=host;user id=user;password=password;port=1433;database=MyDb

If you're hitting a local/dev SQL Server and get x509: negative serial number, that's not this tool — some SQL Server images generate a self-signed cert that Go's stricter TLS parser rejects. Add &encrypt=disable to the connection string to skip TLS for that connection (fine for local/dev, don't do it against anything else).

Failure handling

• A broken statement in schema.sql stops the import immediately — nothing after it runs.
• A broken batch in data-import.sql gets logged and the import keeps going, so one bad table doesn't block the rest of the restore. Exit code is still non-zero if anything failed. All the failures get collected into a summary block at the end, grouped by table, so you don't have to scroll back through thousands of OK lines to find them.
• data-import.sql runs inside one transaction with XACT_ABORT OFF, so a failed statement normally doesn't kill the transaction. A few SQL Server error classes roll it back anyway regardless of that setting (I hit this against real data — some literal-conversion errors do it even with XACT_ABORT OFF); the importer detects that via @@TRANCOUNT and reopens the transaction so the rest of the file still runs, with a warning that the earlier batches in that transaction got undone.

Why it's not just INSERT statements under the hood

Benchmarked against a 3-million-row table (~172 MB of generated SQL):

	Time	Peak memory
Export	7.3s	13.5 MB
Import	38s	13.5 MB

Memory stays flat regardless of table size in both directions — everything streams row by row instead of buffering the file, so a 100 GB table costs about the same RAM as a 1 MB one.

Import is the more interesting half. A literal INSERT INTO t VALUES (...),(...),... statement is capped by SQL Server's own parse/compile cost for that statement, not by network or disk — I checked by running the exact same generated batch both through this tool and raw through sqlcmd, and got matching timings either way (~6,000 rows/sec). At that rate a few-hundred-million-row table takes hours. The only way past it is SQL Server's native bulk-copy protocol (the same one bcp/BULK INSERT use).

data-import.sql itself doesn't change — it's still plain INSERT statements. What changes is how import mode runs it: each batch gets parsed back into typed values and sent via bulk-copy instead of executed as literal SQL, but only when that can be done with real confidence, since I'd rather fall back to the slower proven path than risk getting a value wrong in a backup tool:

• Values are parsed using the actual destination column's SQL type (read from sys.columns), never guessed from what the literal looks like.
• Parsing happens entirely before any database call. Anything that doesn't parse cleanly — an unsupported type (uniqueidentifier, sql_variant, spatial types), non-ASCII content in a non-Unicode char/varchar/text column (the driver has no way to re-encode it into the column's collation codepage, so it isn't safe to guess), or any shape mismatch — falls the whole batch back to plain SQL. Nothing gets sent twice.
• Bulk-copy runs with CHECK_CONSTRAINTS on, so a foreign key or check constraint violation fails the batch the same way a plain INSERT would, instead of silently loading invalid data (SQL Server's bulk-copy protocol skips constraint validation by default).
• A failure after a bulk-copy attempt has already started is reported as a batch failure like any other, never retried as plain SQL — rows might already be on the server, and retrying could double-insert them.

Fixed-length char/nchar/binary columns are supported too — bulk-copy requires the value to be exactly the column's declared length, so a short value (e.g. from ANSI_PADDING OFF) is right-padded to match before it's sent, the same padding SQL Server itself would apply. The one case that still falls back is accented/non-Latin1 text in non-Unicode varchar/char/text columns, per the codepage issue above — correctness over speed: a table full of accented Portuguese text, say, just imports at the slower ~6,000 rows/sec instead of ~79,000, rather than risk mojibake.

What's captured, and what isn't

Captured: tables, columns (type/length/precision/scale/collation), IDENTITY, computed columns, DEFAULT/CHECK/UNIQUE/PRIMARY KEY constraints, non-key indexes (including INCLUDE columns and filtered-index WHERE clauses), foreign keys with ON DELETE/ON UPDATE actions, and data for every SQL Server column type (hierarchyid/geography/geometry/sql_variant included, best-effort).

Not captured, on purpose — this backs up tables and data, not the whole database: views, stored procedures, functions, triggers, permissions, partitioning, full-text indexes, sequences, temporal tables, user-defined types/CLR types.

Development

go build ./...
go vet ./...
go test ./...    # unit tests, fast, no database needed

Integration tests run against a dedicated, disposable SQL Server container — never real data, a separate container on its own port:

./scripts/integration-test.sh test          # starts the container if needed, runs the suite
./scripts/integration-test.sh test -v -run TestExportImportRoundTrip
./scripts/integration-test.sh down          # tears the container down

License

MIT — see LICENSE.

Documentation

Overview

Command sqlserver-import-export exports a SQL Server database's schema and data to two SQL files, and imports them back into another SQL Server.