Moving Data in SQL Server: BULK INSERT, bcp, OPENROWSET, BACPAC Export/Import and Copying Databases

Version note: The examples in this post were run on SQL Server 2025 CU10 (17.0.5035.5, Enterprise Developer Edition) in a Linux container against the series' Bookstore lab database; bcp with mssql-tools18 in the same container; the BACPAC operations with SqlPackage 170.5.96.

Guess First: One Bad Row in 200,000

A publisher sends Bookstore, the online bookstore used as the shared example across this site's series, its price list as a CSV every month: 200,000 rows, a header row, UTF-8, Turkish book titles, comma-containing titles in quotes ("Savaş ve Barış, Cilt 1"). We first load the file into a staging table whose columns are all text:

CREATE TABLE dbo.PriceListStaging
(
    Isbn nvarchar(20) NULL,
    Title nvarchar(200) NULL,
    ListPrice nvarchar(20) NULL,
    Currency nvarchar(10) NULL,
    ValidFrom nvarchar(20) NULL
);

In this month's file, on row 8, the publisher's system wrote a price with a Turkish decimal comma and without quotes: 9780000000007,"Çalıkuşu #7",18,05,TRY,2026-12-01. To be safe we allow up to 100 errors and have bad rows written to a file:

TRUNCATE TABLE dbo.PriceListStaging;
BULK INSERT dbo.PriceListStaging
FROM '/var/opt/mssql/import/prices_v1.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, MAXERRORS = 100,
      ERRORFILE = '/var/opt/mssql/import/prices_v1_errors.txt');
GO
SELECT COUNT(*) AS rows_loaded FROM dbo.PriceListStaging;

How many rows get loaded: 199,999 or 200,000?

Msg 4879, Level 16, State 1, Server sql1, Line 2
Bulk load failed due to invalid column value in CSV data file /var/opt/mssql/import/prices_v1.csv in row 8, column 5.
Msg 7399, Level 16, State 1, Server sql1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Server sql1, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
rows_loaded
-----------
0

9780000000007,"Çalıkuşu #7",18,05,TRY
Row 8 File Offset 397 ErrorFile Offset 0 - HRESULT 0x80004005

Zero. The error file caught the row, but the load still stopped completely. MAXERRORS is for skipping rows whose values can't be converted to the target type; when a row has the wrong number of columns, the CSV parser can't trust the rest of the file and stops. Had the publisher written the same price in quotes as "18,05", the row would load and the problem would only show up in validation. In this post we look at loading data from files with BULK INSERT, bcp and OPENROWSET, the line ending and encoding traps, minimal logging, and moving a whole database as a BACPAC with SqlPackage, by measuring.

BULK INSERT: Whose Machine Is the File On?

BULK INSERT is read by SQL Server itself; the path is resolved on the machine where SQL Server runs, not the one that sends the query. In a container that means the container's file system:

BULK INSERT dbo.PriceListStaging
FROM 'C:\Users\user\Downloads\prices.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2);

Msg 4860, Level 16, State 1, Server sql1, Line 1
Cannot bulk load. The file "C:\Users\user\Downloads\prices.csv" does not exist or you don't have file access rights. See https://go.microsoft.com/fwlink/?LinkId=2374938 for more information.

I copied the file into the container with docker cp (you could also put it on a volume); a SQL Server on Windows reads a file on the network by its UNC path (\\server\share\file), and the SQL Server service account needs access to that share. The permissions needed are INSERT and ADMINISTER BULK OPERATIONS. On Linux this permission and the bulkadmin role are supported since SQL Server 2025 CU3; before that only sysadmin could load.

FORMAT = 'CSV' (since SQL Server 2017) applies the RFC 4180 rules: a comma inside quotes isn't a field separator, and the quote character can be changed with FIELDQUOTE. FIRSTROW = 2 skips the header row. Without this option, a CSV loaded with only FIELDTERMINATOR = ',' splits the title "Savaş ve Barış, Cilt 1" into two columns.

Encoding and Line Endings

On SQL Server for Windows, a UTF-8 file is read with CODEPAGE = '65001'; without it the default OEM code page is used and Turkish characters break. The same option doesn't exist on Linux:

TRUNCATE TABLE dbo.PriceListStaging;
BULK INSERT dbo.PriceListStaging
FROM '/var/opt/mssql/import/prices.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, CODEPAGE = '65001');

Msg 16202, Level 15, State 3, Server sql1, Line 4
Keyword or statement option 'CODEPAGE' is not supported on the 'Linux' platform.

On Linux, the load without CODEPAGE read the Turkish characters of this file correctly (you can see it in the outputs below). So the same script doesn't behave the same on the two platforms: CODEPAGE is required on Windows and an error on Linux. Write your script knowing which server it will run on.

The second trap is the line ending. Files produced on Linux and macOS end lines with LF only, while Excel's "CSV UTF-8" output ends them with CRLF. I loaded the file from Excel with an LF terminator:

TRUNCATE TABLE dbo.PriceListStaging;
BULK INSERT dbo.PriceListStaging
FROM '/var/opt/mssql/import/prices_excel.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, ROWTERMINATOR = '0x0a');
SELECT TOP (2) Isbn, Title, ValidFrom, LEN(ValidFrom) AS len,
       CAST(RIGHT(ValidFrom, 1) AS varbinary(2)) AS last_char, TRY_CONVERT(date, ValidFrom) AS valid_from
FROM dbo.PriceListStaging WHERE Isbn IN ('9780000000001', '9780000000002');

Isbn           Title               ValidFrom    len  last_char  valid_from
-------------  ------------------  -----------  ---  ---------  ----------
9780000000001  Şeker Portakalı #1  2026-12-01  11   0x0D00     NULL
9780000000002  Küçük Prens #2      2026-12-01  11   0x0D00     NULL

All 200,000 rows loaded without a single error, but the last column is 11 characters: an invisible CR (0x0D) stayed at the end of the value and the date conversion silently returned NULL. With the right terminator:

TRUNCATE TABLE dbo.PriceListStaging;
BULK INSERT dbo.PriceListStaging
FROM '/var/opt/mssql/import/prices_excel.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, ROWTERMINATOR = '0x0d0a');
SELECT TOP (2) Isbn, Title, ValidFrom, LEN(ValidFrom) AS len, TRY_CONVERT(date, ValidFrom) AS valid_from
FROM dbo.PriceListStaging WHERE Isbn IN ('9780000000001', '9780000000002');

Isbn           Title               ValidFrom   len  valid_from
-------------  ------------------  ----------  ---  ----------
9780000000001  Şeker Portakalı #1  2026-12-01  10   2026-12-01
9780000000002  Küçük Prens #2      2026-12-01  10   2026-12-01

The reason for writing the terminator in hex is a note on Microsoft Learn: on Windows, \n is automatically replaced with \r\n, so ROWTERMINATOR = '\n' in the same script means CRLF on Windows and LF on Linux. 0x0a and 0x0d0a mean the same thing everywhere. The UTF-8 BOM at the start of the Excel file caused no trouble in these tests: it's skipped together with the header row, and in a file without a header it didn't end up in the first ISBN either. Still, look at the first value of the first row during validation.

Staging, Validation and Applying

We loaded the file the publisher fixed (the price is now in quotes as "18,05"). The staging table is a heap and all its columns are text; no row is rejected for its type, and validation happens in SQL:

TRUNCATE TABLE dbo.PriceListStaging;
BULK INSERT dbo.PriceListStaging
FROM '/var/opt/mssql/import/prices.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, ROWTERMINATOR = '0x0a', TABLOCK);

SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN NULLIF(s.Isbn, '') IS NULL THEN 1 ELSE 0 END) AS missing_isbn,
    SUM(CASE WHEN TRY_CONVERT(decimal(10,2), s.ListPrice) IS NULL THEN 1 ELSE 0 END) AS bad_price,
    SUM(CASE WHEN TRY_CONVERT(date, s.ValidFrom) IS NULL THEN 1 ELSE 0 END) AS bad_date,
    SUM(CASE WHEN b.BookId IS NOT NULL THEN 1 ELSE 0 END) AS known_books,
    SUM(CASE WHEN b.BookId IS NULL AND NULLIF(s.Isbn, '') IS NOT NULL THEN 1 ELSE 0 END) AS new_titles
FROM dbo.PriceListStaging AS s
LEFT JOIN dbo.Books AS b ON b.Isbn = s.Isbn;
SELECT TOP (3) s.Isbn, s.Title, s.ListPrice
FROM dbo.PriceListStaging AS s
WHERE TRY_CONVERT(decimal(10,2), s.ListPrice) IS NULL;

total_rows  missing_isbn  bad_price  bad_date  known_books  new_titles
----------  ------------  ---------  --------  -----------  ----------
200000      4             10         0         39999        159997

Isbn           Title            ListPrice
-------------  ---------------  ---------
9780000000007  Çalıkuşu #7      18,05
9780000020007  Çalıkuşu #20007  37,51
9790000040007  Çalıkuşu #40007  24,33

The validation report is a single query: 4 rows have no ISBN, 10 prices were written with a decimal comma, 39,999 books are in the catalog, and 159,997 titles aren't sold by us. The report goes back to the publisher; the valid rows are applied:

SET XACT_ABORT ON;
BEGIN TRANSACTION;
UPDATE b
SET b.Price = TRY_CONVERT(decimal(10,2), s.ListPrice)
FROM dbo.Books AS b
JOIN dbo.PriceListStaging AS s ON s.Isbn = b.Isbn
WHERE TRY_CONVERT(decimal(10,2), s.ListPrice) > 0
  AND s.Currency = 'TRY'
  AND b.Price <> TRY_CONVERT(decimal(10,2), s.ListPrice);
SELECT @@ROWCOUNT AS prices_updated;
COMMIT;

prices_updated
--------------
39997

Two of the 39,999 known books had a bad price; 39,997 prices were updated. The application-side counterpart of this flow, a design that takes the upload from the user on the site and produces a per-row error file, is in the "Bulk Import and Validation Report" post of the Solution Patterns series; the details of UPDATE ... FROM and upserts with MERGE are in post 15 of the SQL Fundamentals series. If the application reads the file itself, the fastest way to send the data to the server is SqlBulkCopy (the performance post of the Dapper series); the server-side file path and permission issues go away too.

TABLOCK and Minimal Logging

I loaded the same 200,000 rows four times and measured the log each load produced:

SET NOCOUNT ON;
ALTER DATABASE sqls_23 SET RECOVERY FULL;
BACKUP DATABASE sqls_23 TO DISK = '/var/opt/mssql/backup/sqls_23_full.bak' WITH COMPRESSION, INIT;
DECLARE @r TABLE (recovery_model nvarchar(20), options varchar(20), log_mb decimal(8,1));
DECLARE @i int = 1, @rm nvarchar(20), @tl bit;
WHILE @i <= 4
BEGIN
    SELECT @rm = CASE WHEN @i <= 2 THEN N'FULL' ELSE N'BULK_LOGGED' END, @tl = @i % 2;
    IF @i = 3 ALTER DATABASE sqls_23 SET RECOVERY BULK_LOGGED;
    TRUNCATE TABLE dbo.PriceListStaging;
    BACKUP LOG sqls_23 TO DISK = '/var/opt/mssql/backup/sqls_23_log.trn' WITH INIT;
    IF @tl = 1
        BULK INSERT dbo.PriceListStaging FROM '/var/opt/mssql/import/prices.csv'
        WITH (FORMAT = 'CSV', FIRSTROW = 2, ROWTERMINATOR = '0x0a', TABLOCK);
    ELSE
        BULK INSERT dbo.PriceListStaging FROM '/var/opt/mssql/import/prices.csv'
        WITH (FORMAT = 'CSV', FIRSTROW = 2, ROWTERMINATOR = '0x0a');
    INSERT @r SELECT @rm, CASE WHEN @tl = 1 THEN 'TABLOCK' ELSE '-' END, log_since_last_log_backup_mb FROM sys.dm_db_log_stats(DB_ID());
    SET @i += 1;
END;
ALTER DATABASE sqls_23 SET RECOVERY SIMPLE;
SELECT * FROM @r;

recovery_model  options  log_mb
--------------  -------  ------
FULL            TABLOCK  26.3
FULL            -        43.6
BULK_LOGGED     TABLOCK  .5
BULK_LOGGED     -        43.4

Minimal logging (logging only page allocations instead of rows) needs its conditions together: the SIMPLE or BULK_LOGGED recovery model, a heap target (or an empty index under certain conditions), and TABLOCK. With all three, 200,000 rows produced half a megabyte of log; with one missing, 43 MB. In FULL, TABLOCK reduced the log, but the rows are still logged. That's why the staging table is a heap without indexes; switching to BULK_LOGGED and back for a bulk load costs, as we saw in post 20, point-in-time restore within the log backup that covers that interval. Keeping the staging table in a separate database in SIMPLE recovery removes that cost.

OPENROWSET(BULK): Querying a File Without Loading It

OPENROWSET(BULK ...) reads a file like a table; it's handy for looking at a file, filtering it or joining it with the catalog before loading. Describing the columns needs a format file; bcp generates it from the staging table:

bcp sqls_23.dbo.PriceListStaging format /dev/null -c -t, -r 0x0a -f prices.fmt -S localhost -U sa -P "$MSSQL_SA_PASSWORD" -u

14.0
5
1       SQLCHAR             0       40      ","    1     Isbn                                     SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       400     ","    2     Title                                    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       40      ","    3     ListPrice                                SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       20      ","    4     Currency                                 SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR             0       40      "\n"   5     ValidFrom                                SQL_Latin1_General_CP1_CI_AS

bcp on Linux doesn't produce an XML format file (-x); this is the classic text format. I queried the five books whose price will rise the most, without loading:

SELECT TOP (5) p.Isbn, p.Title, b.Price AS current_price, p.ListPrice AS new_price
FROM OPENROWSET(BULK '/var/opt/mssql/import/prices.csv',
                FORMAT = 'CSV', FIRSTROW = 2,
                FORMATFILE = '/var/opt/mssql/import/prices.fmt') AS p
JOIN dbo.Books AS b ON b.Isbn = p.Isbn
ORDER BY TRY_CONVERT(decimal(10,2), p.ListPrice) - b.Price DESC;

Isbn           Title                            current_price  new_price
-------------  -------------------------------  -------------  ---------
9780000000431  Şeker Portakalı #431           49.99          53.99
9780000003493  Savaş ve Barış, Cilt 1 #3493  49.95          53.95
9780000004044  Yüzyıllık Yalnızlık #4044   49.96          53.96
9780000004675  Gurur ve Önyargı #4675         49.97          53.97
9780000005728  Ä°nce Memed #5728                49.99          53.99

The numbers are right, the text is broken. The SQLCHAR fields in the format file read the UTF-8 bytes with a single-byte code page; changing the collation in the format file to a UTF-8 collation (Latin1_General_100_CI_AS_SC_UTF8) didn't change the result in this test. Since the same file loads correctly into the nvarchar staging table with BULK INSERT, on Linux use OPENROWSET to check ASCII columns such as ISBN, price and date, and read the text from staging. On Windows, OPENROWSET also accepts CODEPAGE = '65001'.

bcp: Bulk Copy on the Client Side

Unlike BULK INSERT, bcp reads and writes the file on the machine it runs on and sends the data to the server over the network. I exported the classic books to a tab-separated file and loaded it back into another table:

bcp "SELECT Isbn, Title, Price FROM dbo.Books WHERE Category = 'Classic'" queryout classics.tsv -c -t "\t" -d sqls_23 -S localhost -U sa -P "$MSSQL_SA_PASSWORD" -u -b 5000
bcp dbo.ClassicsCopy in classics.tsv -c -t "\t" -d sqls_23 -S localhost -U sa -P "$MSSQL_SA_PASSWORD" -u -b 5000 -h "TABLOCK"

Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
...
22000 rows copied.

Starting copy...
5000 rows sent to SQL Server. Total sent: 5000
...
22000 rows copied.

-c is character mode, -t the field separator, -b 5000 a commit every 5,000 rows (if a big load is interrupted you lose at most one batch), -h "TABLOCK" the minimal logging hint, and -u trusts the server certificate in mssql-tools18. bcp doesn't understand CSV quotes; for quoted CSV use BULK INSERT ... FORMAT = 'CSV', and keep bcp for tab-separated or native (-n) files it produces itself. A password given with -P on the command line shows up in the process list; on a real server prefer Windows Authentication or a Microsoft Entra identity.

Moving a Database: BACPAC and an Anonymized Developer Copy

The development team wants realistic data. Putting production data on a developer's machine as is is wrong for both security and KVKK, Turkey's personal data protection law; personal data sits in the development environment without any of production's protections. The flow: restore a COPY_ONLY backup of production to a side database (post 20), anonymize personal data there, and take the BACPAC from there:

UPDATE dbo.Customers
SET FirstName = CONCAT(N'Customer', CustomerId),
    LastName = N'Test',
    Email = CONCAT('customer', CustomerId, '@example.test'),
    City = N'-';
SELECT TOP (2) CustomerId, FirstName, LastName, Email, City FROM dbo.Customers ORDER BY CustomerId;

CustomerId  FirstName  LastName  Email                   City
----------  ---------  --------  ----------------------  ----
1           Customer1  Test      customer1@example.test  -
2           Customer2  Test      customer2@example.test  -

sqlpackage /Action:Export /SourceServerName:"localhost,14333" /SourceDatabaseName:sqls_23_anon /SourceUser:sa /SourcePassword:"$MSSQL_SA_PASSWORD" /SourceTrustServerCertificate:True /TargetFile:bookstore-dev.bacpac
sqlpackage /Action:Import /TargetServerName:"localhost,14333" /TargetDatabaseName:sqls_23_dev /TargetUser:sa /TargetPassword:"$MSSQL_SA_PASSWORD" /TargetTrustServerCertificate:True /SourceFile:bookstore-dev.bacpac

Connecting to database 'sqls_23_anon' on server 'localhost,14333'.
...
Successfully exported database and saved it to file '/home/user/bookstore/bookstore-dev.bacpac'.

Importing to database 'sqls_23_dev' on server 'localhost,14333'.
...
Disabling index 'UX_Customers_Email'.
...
Enabling index 'UX_Customers_Email'.
...
Successfully imported database.

SELECT d.name, CAST(SUM(f.size) * 8 / 1024 AS int) AS data_mb, COUNT(*) AS files,
       (SELECT COUNT(*) FROM sqls_23_dev.dbo.Orders) AS orders,
       (SELECT TOP (1) Email FROM sqls_23_dev.dbo.Customers ORDER BY CustomerId) AS first_email
FROM sys.databases AS d JOIN sys.master_files AS f ON f.database_id = d.database_id AND f.type = 0
WHERE d.name = 'sqls_23_dev'
GROUP BY d.name;

name         data_mb  files  orders   first_email
-----------  -------  -----  -------  ----------------------
sqls_23_dev  456      1      1000000  customer1@example.test

A BACPAC is a zip file containing the schema and each table's data; for this database it was 75 MB (the compressed full backup in post 20 was 112 MB). During import SqlPackage first disables the indexes and loads the data, then rebuilds the indexes. How much anonymization is enough (e-mail and name aren't; addresses, phone numbers, birth dates and free-text notes are personal data too) is the topic of the upcoming Data Privacy and KVKK series.

The consistency warning in the table matters: according to Microsoft's SqlPackage documentation, an export should be taken from a database with no writes during the export or from a consistent copy; otherwise tables are read at different moments and rows that break foreign keys can appear. That's the second reason we took the export from the restored copy in the flow above.

What's different in SQL Server 2019/2022: FORMAT = 'CSV' has existed since 2017. On Linux, CODEPAGE accepted only the 'RAW' value in 2019, and in this post 2025 rejected '65001'. BULK INSERT has been able to read files in Azure Blob Storage since 2017; in 2022, data virtualization made CSV and Parquet files in S3-compatible storage queryable too; in 2025, querying CSV, Parquet and Delta files no longer needs a separate PolyBase service.

Watch Out in Production

Load into staging, validate, then apply in a single transaction. Keep the validation report as an output as important as the load itself.

Split a big load into batches (BATCHSIZE, bcp -b). One huge transaction grows the log, and if it's interrupted everything is rolled back.

Check the file's encoding and line endings before loading, and write down the format of the publisher's file as a contract: separator, quotes, decimal separator, encoding.

Common Pitfalls

Thinking MAXERRORS skips every error. A CSV row with the wrong number of columns stops the load completely.

Forgetting that the file path is resolved on SQL Server's machine. In a container the path is the container's file system; put the file on a volume or copy it in with docker cp.

Missing the line ending difference. A CRLF file loaded with LF doesn't fail; an invisible CR stays at the end of every row's last column and conversions silently return NULL.

Assuming the same script behaves the same on Windows and Linux. CODEPAGE is needed on Windows and an error on Linux; \n is CRLF on Windows and LF on Linux.

Copying production data to the development environment without anonymizing it.

A Small Experiment

★☆☆ Load the price list without FORMAT = 'CSV', using only FIELDTERMINATOR = ','. What does the "Savaş ve Barış, Cilt 1" row look like?

★★☆ Add a clustered index to the staging table and repeat the BULK_LOGGED + TABLOCK measurement. How does the log change when the table is empty and when it's full?

★★★ With SqlPackage's /p:TableData property, take a BACPAC with only the catalog tables (Authors, Books, StockItems) and compare its size with the full BACPAC. What does import say when you leave out a table that has foreign keys?

We've seen moving data; what remains is moving schema. In the next and final post of the series we'll compare managing schema changes with a SQL Database Project and DACPAC (state-based) and with DbUp and Flyway (migration-based).