SQL Server table-level export/import (bcp, sqlpackage)

Not a backup but logical data movement: exporting tables with bcp and loading them into another database (native or character format, format file, batch, TABLOCK, identity column, UTF-8), writing a query result to a CSV/text file (queryout), moving schema + data with sqlpackage (.bacpac Export/Import, e.g. migration to Azure SQL Database) and schema only (.dacpac Extract, Script, Publish). Includes installing mssql-tools18 and sqlpackage on Linux. At the end you have tables on the target whose row counts are verified against the source, or a file you can share.

If you are moving the whole database to the same or a newer SQL Server, backup/restore is faster and consistent: SQL Server backup and restore.

Topology: 1 server (commands run on the SQL Server itself or on a management server)

Scenarios

• Move tables: bcp out → bcp in — Copy one or more tables to another SQL Server database; the tables must already exist on the target.
• Write a query result to a file: bcp queryout (CSV/text) — Write the result of a query to a CSV/text file to hand over to another system (Excel, ETL, reporting).
• Schema + data: sqlpackage Export / Import (.bacpac) — Move schema and data in a single file; especially for migration to Azure SQL Database or small/medium databases.
• Schema only: sqlpackage Extract / Script / Publish (.dacpac) — Extract only the schema, compare it with the target and generate a change script, or deploy an empty schema to a new environment.

Before you start

• The right tool is chosen: - bcp (native): moving one or more large tables from SQL Server to SQL Server; the fastest option. The target tables must already exist. - bcp (character) / queryout: handing text/CSV to other systems (ETL, reporting, Excel). - .bacpac (Export/Import): moving schema + data in a single file; especially migration to Azure SQL Database. Slow on large databases (hundreds of GB); in that case prefer backup/restore or bcp. - .dacpac (Extract): schema only (tables, views, procedures); schema comparison and deploying an empty schema to a new environment.
• … has read permission on the source (SELECT on the tables; db_owner recommended for Export/Extract) and write permission on the target (INSERT + ALTER TABLE for bcp in; dbcreator or db_owner for Import/Publish). Provide the password before the script: read -rsp 'Password: ' SQLCMDPASSWORD; export SQLCMDPASSWORD and sudo --preserve-env=SQLCMDPASSWORD bash <script>.sh.
• PowerShell is opened as administrator (Start → "PowerShell" → right-click → Run as administrator). …The Windows account that opened PowerShell…The … login… has read permission on the source and write permission on the target (INSERT + ALTER TABLE for bcp in; dbcreator or db_owner for Import/Publish).
• The source (…)… and target (…)… SQL Servers are reachable over TCP from the command server.
• The source (…)… and target (…)… SQL Servers are reachable over TCP from the command server; bcp and sqlcmd are in PATH (they come with the SQL Server installation or with "Microsoft Command Line Utilities for SQL Server").
• The disk holding the export directory (…) has as much free space as the data being transferred (a native file is roughly the data size of the table; a character file can be larger; a .bacpac is compressed).
• The drive holding the export directory (…) has as much free space as the data being transferred.
• The tables have been created in the target database (…) with the same structure (bcp does not create tables). The easy way to move the schema is the Schema only (.dacpac) option of this guide. For large loads, nonclustered indexes and triggers on the target table can be disabled, and foreign keys checked afterwards.
• There are no writes on the source during the export (the application is stopped), or a copy of the database (restored from a COPY_ONLY backup, or a database snapshot) is used as the source. bcp and sqlpackage Export are not transactionally consistent: tables that change during the export can be inconsistent with each other.

Phases of the guide

• Tools and preparation — Install the sqlcmd and bcp tools (mssql-tools18) · Install sqlpackage · Check the bcp and sqlcmd tools · Install sqlpackage · Create the export directory · Create the export directory
• Table transfer (bcp out / in) — Record the row count and size of the source tables · Generate the format files · Generate the format files · Export the tables (bcp out) · Export the tables (bcp out) · Check the target tables …
• Exporting a query result (queryout) — Write the query result to a file (bcp queryout) · Write the query result to a file (bcp queryout)
• Schema + data (.bacpac) — Export the database as a .bacpac (Export) · Export the database as a .bacpac (Export) · Create the target database (if it does not exist) · Load the .bacpac into the target (Import) · Load the .bacpac into the target (Import) · Check the tables and row counts in the target database
• Schema only (.dacpac) — Extract the schema as a .dacpac (Extract) · Extract the schema as a .dacpac (Extract) · Create the target database (if it does not exist) · Generate a change script against the target (Script) · Generate a change script against the target (Script) · Apply the schema to the target (Publish) …
• Afterwards — Clean up the export files · Clean up the export files