SQL Server Examples

Comprehensive examples for exporting data from SQL Server (MSSQL, OLEDB, ODBC).

ADBC Support

SQL Server also supports the adbc_mssql connection type, powered by the Arpe.io ADBC FastDriver for ultra-fast, Arrow-native Parquet exports. Output is Parquet-only — see the ADBC Examples section below.

Single-threaded Export

Simple export without parallelization for smaller datasets.

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "FASTExportData" `
  --trusted `
  --query "SELECT top 1000 * FROM dbo.TEST_71_1M_12m" `
  --directory "C:\temp" `
  --fileoutput "TEST1000.csv" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "None"

Parallel Export - Random Method

Export using Random distribution based on modulo operation.

Windows Authentication

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "FASTExportData" `
  --trusted `
  --query "SELECT * FROM dbo.TEST_71_1M_12m" `
  --directory "C:\temp" `
  --fileoutput "TEST_71c_1Mr.csv" `
  --decimalseparator "," `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "Random" `
  --distributekeycolumn "ID_ARTICLE" `
  --paralleldegree 8 `
  --merge "False" `
  --runid "mssql_to_csv_parallel8_random"

SQL Authentication

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "FASTExportData" `
  --user "FastUser" `
  --password "FastPassword" `
  --query "SELECT * FROM dbo.TEST_71_1M_12m" `
  --directory "C:\temp" `
  --fileoutput "TEST_71c_1Mr.csv" `
  --decimalseparator "," `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "Random" `
  --distributekeycolumn "ID_ARTICLE" `
  --paralleldegree 8 `
  --merge "False" `
  --runid "mssql_to_csv_parallel8_random"

Parallel Export - Physical Method

Parallel export using the physical row location (%%physloc%%), without a distribute key column.

Physical on SQL Server

With a SQL Server connection type (mssql, msoledbsql, adbc_mssql), the Physical method resolves to the Physloc strategy (formerly --parallelmethod Physloc, now deprecated). --sourceschema and --sourcetable are required. On a clustered columnstore table, FastBCP detects it and splits on the columnstore row id.

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "tpch10_collation_bin2" `
  --trusted `
  --sourceschema "dbo" `
  --sourcetable "orders" `
  --directory "D:\temp" `
  --fileoutput "mssql_orders.parquet" `
  --parallelmethod "Physical" `
  --paralleldegree -2 `
  --merge "False" `
  --runid "mssql_to_parquet_parallel-2_physical"

Parallel Export - RangeId Method

Export using RangeId distribution for evenly distributed integer keys.

Using table name:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "tpch10_collation_bin2" `
  --trusted `
  --sourceschema "dbo" `
  --sourcetable "orders" `
  --directory "D:\temp" `
  --fileoutput "mssql_orders.csv" `
  --decimalseparator "," `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "RangeId" `
  --distributekeycolumn "o_orderkey" `
  --paralleldegree -2 `
  --merge "False" `
  --runid "mssql_to_csv_parallel-2_rangeid"

Using query with filter:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "tpch10_collation_bin2" `
  --trusted `
  --query "select * from orders WHERE o_orderdate between '19950101' and '19951231'" `
  --directory "D:\temp" `
  --fileoutput "mssql_orders.csv" `
  --decimalseparator "," `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "RangeId" `
  --distributekeycolumn "o_orderkey" `
  --paralleldegree -2 `
  --merge "False" `
  --runid "mssql_to_csv_parallel-2_rangeid_using_query"

Parallel Export - Ntile Method

Export using Ntile distribution for evenly distributed chunks.

Full table export:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "tpch10_collation_bin2" `
  --trusted `
  --sourceschema "dbo" `
  --sourcetable "orders" `
  --directory "D:\temp" `
  --fileoutput "mssql_orders.csv" `
  --decimalseparator "," `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "Ntile" `
  --distributekeycolumn "o_orderkey" `
  --paralleldegree 12 `
  --merge "False" `
  --runid "mssql_to_csv_parallel12_Ntile"

Filtered export with date column:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "tpch10_collation_bin2" `
  --trusted `
  --query "select * from orders WHERE o_orderdate between '19950101' and '19951231'" `
  --directory "D:\temp" `
  --fileoutput "mssql_orders.csv" `
  --decimalseparator "," `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "Ntile" `
  --distributekeycolumn "o_orderdate" `
  --paralleldegree 12 `
  --merge "False" `
  --runid "mssql_to_csv_parallel12_Ntile_using_query"

Parallel Export - DataDriven Method

Export using DataDriven distribution based on distinct values of a column.

Using partition function:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "tpch10_collation_bin2" `
  --directory "D:\temp" `
  --fileoutput "mssql_orders.csv" `
  --trusted `
  --sourceschema "dbo" `
  --sourcetable "orders_part" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "DataDriven" `
  --distributekeycolumn "`$partition.PF_DATE(o_orderdate)" `
  --merge "False" `
  --paralleldegree 10

Export to Parquet

Export SQL Server data to Parquet format.

Single-threaded:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "faker" `
  --trusted `
  --sourceschema "dbo" `
  --sourcetable "faker_s1000" `
  --directory "D:\temp" `
  --fileoutput "mssql_faker_s1000.parquet" `
  --parallelmethod "None" `
  --runid "mssql_to_parquet_NoParallel"

Parallel with RangeId:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --trusted `
  --server "localhost" `
  --database "tpch10_collation_bin2" `
  --directory "D:\temp" `
  --fileoutput "mssql_orders.parquet" `
  --parallelmethod "RangeId" `
  --distributekeycolumn "o_orderkey" `
  --paralleldegree 7 `
  --sourceschema "dbo" `
  --sourcetable "orders" `
  --merge "False"

Parallel with Ntile and query:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --trusted `
  --server "localhost" `
  --database "tpch10_collation_bin2" `
  --directory "D:\temp" `
  --fileoutput "mssql_orders_1995.parquet" `
  --parallelmethod "Ntile" `
  --distributekeycolumn "o_orderkey" `
  --paralleldegree 7 `
  --query "select * from dbo.orders where o_orderdate between '1995-01-01' and '1995-12-31'" `
  --merge "False"

OLEDB Connection

Alternative connection method using OLEDB provider.

Single-threaded:

$query="SELECT top 10000 * FROM FASTExportData.dbo.TEST_71_1M_12m"
$target="localhost"

.\FastBCP.exe `
  --connectiontype "oledb" `
  --provider "MSOLEDBSQL" `
  --server $target `
  --directory "C:\temp" `
  --fileoutput "TEST.CSV" `
  --paralleldegree 1 `
  --trusted `
  --query $query `
  --parallelmethod "None" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8"

Parallel Random with Windows Auth:

.\FastBCP.exe `
  --connectiontype "oledb" `
  --provider "MSOLEDBSQL" `
  --server $target `
  --directory "C:\temp" `
  --fileoutput "TEST.CSV" `
  --paralleldegree 6 `
  --trusted `
  --query $query `
  --parallelmethod "Random" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --distributekeycolumn "ID_ARTICLE" `
  --merge "False"

Parallel Random with SQL Auth:

.\FastBCP.exe `
  --connectiontype "oledb" `
  --provider "MSOLEDBSQL" `
  --server $target `
  --directory "C:\temp" `
  --fileoutput "TEST.CSV" `
  --paralleldegree 6 `
  --user "FastUser" `
  --password "FastPassword" `
  --query $query `
  --parallelmethod "Random" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --distributekeycolumn "ID_ARTICLE" `
  --merge "False"

Parallel DataDriven:

.\FastBCP.exe `
  --connectiontype "oledb" `
  --provider "MSOLEDBSQL" `
  --server $target `
  --directory "C:\temp" `
  --fileoutput "TEST.CSV" `
  --paralleldegree 6 `
  --trusted `
  --query $query `
  --parallelmethod "DataDriven" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --distributekeycolumn "ID_DECOUPE_GEO" `
  --merge "False"

ODBC Connection

Using ODBC DSN for connection.

Single-threaded:

.\FastBCP.exe `
  --connectiontype "odbc" `
  --dsn "DSN4FastBCP" `
  --directory "C:\temp" `
  --fileoutput "TEST.CSV" `
  --paralleldegree 1 `
  --trusted `
  --query "SELECT * FROM [TEST_71_1M_12m]" `
  --parallelmethod "None" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8"

Parallel Random with Windows Auth:

.\FastBCP.exe `
  --connectiontype "odbc" `
  --dsn "DSN4FastBCP" `
  --directory "C:\temp" `
  --fileoutput "TEST.CSV" `
  --paralleldegree 6 `
  --trusted `
  --query "SELECT * FROM [TEST_71_1M_12m]" `
  --parallelmethod "Random" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --distributekeycolumn "ID_ARTICLE" `
  --merge "False"

Parallel Random with SQL Auth:

.\FastBCP.exe `
  --connectiontype "odbc" `
  --dsn "DSN4FastBCP" `
  --directory "C:\temp" `
  --fileoutput "TEST.CSV" `
  --paralleldegree 6 `
  --user "FastUser" `
  --password "FastPassword" `
  --query "SELECT * FROM [TEST_71_1M_12m]" `
  --parallelmethod "Random" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --distributekeycolumn "ID_ARTICLE" `
  --merge "False"

Parallel DataDriven:

.\FastBCP.exe `
  --connectiontype "odbc" `
  --dsn "DSN4FastBCP" `
  --directory "C:\temp" `
  --fileoutput "TEST.CSV" `
  --paralleldegree 6 `
  --trusted `
  --query "SELECT * FROM [TEST_71_1M_12m]" `
  --parallelmethod "DataDriven" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --distributekeycolumn "ID_GEO" `
  --merge "False"

ADBC Examples

The adbc_mssql connection type uses the Arpe.io ADBC FastDriver for ultra-fast, Arrow-native exports. Output is Parquet-only — see Connection Parameters for details.

Single-threaded export:

./FastBCP \
  --connectiontype "adbc_mssql" \
  --server "localhost,1433" \
  --database "tpch" \
  --user "migadmin" \
  --password "FastPassword" \
  --sourceschema "tpch10" \
  --sourcetable "lineitem" \
  --directory "./" \
  --fileoutput "{sourcetable}.parquet" \
  --method "None"

Parallel export with RangeId and license file:

.\FastBCP.exe `
  --connectiontype "adbc_mssql" `
  --server "localhost" `
  --trusted `
  --database "TPCH_SF100_NOBACKUP" `
  --sourceschema "dbo" `
  --sourcetable "lineitem_ci" `
  --fileoutput "{sourcetable}.parquet" `
  --directory "B:\export\{sourcetable}\" `
  --parquetcompression "None" `
  --parallelmethod "RangeId" `
  --distributekeycolumn "l_orderkey" `
  --degree 32 `
  --runid "1.2.0" `
  --licence "C:\Sources\FastBCP\FastBCP_AetP.lic"

SQL Server Backup Files (backup_mssql)

The backup_mssql connection type reads a table straight from a SQL Server full backup (.bak), without restoring it and without a SQL Server instance. --server is the directory of the backups and --database the backup file. Output is Parquet-only — see SQL Server backup files for what is supported.

One backup file, parallel export (Physical resolves to Pages):

Windows

.\FastBCP.exe `
  --connectiontype "backup_mssql" `
  --server "D:\Backups\MSSQL" `
  --database "tpch_full.bak" `
  --sourceschema "dbo" `
  --sourcetable "orders" `
  --directory "D:\export\{sourcetable}" `
  --fileoutput "{sourcetable}.parquet" `
  --parallelmethod "Physical" `
  --paralleldegree 8

Linux

./FastBCP \
  --connectiontype "backup_mssql" \
  --server "/var/opt/mssql/backup" \
  --database "tpch_full.bak" \
  --sourceschema "dbo" \
  --sourcetable "orders" \
  --directory "/export/{sourcetable}" \
  --fileoutput "{sourcetable}.parquet" \
  --parallelmethod "Physical" \
  --paralleldegree 8

Striped backup (BACKUP DATABASE tpch TO DISK = 'tpch_1.bak', DISK = 'tpch_2.bak', DISK = 'tpch_3.bak'): a pattern matching the stripes, or the stripes separated by ;, in any order:

./FastBCP \
  --connectiontype "backup_mssql" \
  --server "/var/opt/mssql/backup" \
  --database "tpch_*.bak" \
  --sourceschema "dbo" \
  --sourcetable "lineitem" \
  --directory "/export/lineitem" \
  --fileoutput "lineitem.parquet" \
  --parallelmethod "Physical" \
  --paralleldegree 8

  --database "tpch_1.bak;tpch_2.bak;tpch_3.bak"

A query on one table (column list, no filter: SELECT * | columns FROM [schema.]table):

.\FastBCP.exe `
  --connectiontype "backup_mssql" `
  --server "D:\Backups\MSSQL" `
  --database "sales_full.bak" `
  --query "SELECT OrderID, CustomerID, OrderDate FROM Sales.Orders" `
  --directory "D:\export\orders" `
  --fileoutput "orders.parquet"

note

A file holding several backups (WITH NOINIT) is read at its last full backup. Backups of TDE databases and encrypted backups (WITH ENCRYPTION) are refused with a clear message.

tip

Build your command with the Wizard