On a recent cloud migration, I needed to migrate just one year of one table of data from a client’s servers to another SQL Server hosted in a VM in the cloud. The data structure already existed, I just needed to move the data to it. Our problem was that the VM that we were moving into didn’t actually have enough space to restore the entire database, so a full BACKUP/RESTORE/DELETE wasn’t an option. I used bcp for this since it wouldn’t require me to install anything on the client’s server.
One thing I ran into before anything else: bcp threw a certificate chain error when connecting to the local instance. The fix is the -u flag, which tells bcp to trust the server certificate without validating the chain. I added it to every command after that.
Worth knowing that -u needs bcp version 18 or later, which ships with SQL Server 2025. Run bcp -v to see what you have. On an older bcp the flag doesn’t exist and you’d be looking at -H to match the hostname in the certificate, or fixing the certificate itself.
The first thing I did was generate a format file. This maps the columns in the export to the columns in the destination table, and it’s worth doing first so you can inspect it before any data moves.
bcp AdventureWorks2022.Person.Person format nul -S localhost -T -n -f person.fmt -uYou won’t get any output in the CMD window from this if the format file was successfully exported. Once it’s exported you can review the output by opening the .fmt file in Notepad, and it should look something like this:
One thing to watch is if you use a three-part name like AdventureWorks2022.Person.Person, don’t also pass -d with the database name. bcp will error telling you that you can’t specify the database name twice.
Before running the full year I tested with one week to make sure the format file was right and the row counts matched.
bcp "SELECT * FROM AdventureWorks2022.Person.Person WHERE ModifiedDate >= '2013-01-01' AND ModifiedDate < '2013-01-08'" queryout person_test.bcp -S localhost -T -f person.fmt -u
Once that checked out, I ran the full year:
bcp "SELECT * FROM AdventureWorks2022.Person.Person WHERE ModifiedDate >= '2013-01-01' AND ModifiedDate < '2014-01-01'" queryout person_2013.bcp -S localhost -T -f person.fmt -u
I’m always pleasantly surprised by how fast bcp is. This export of 27,000 rows took about 3 seconds. I’ve had exports of millions of rows take just a few minutes.
I copied the .bcp and .fmt files to the destination machine, then ran the import. The table had an identity column and I needed to keep the original values, so I passed -E. I also set the destination database to simple recovery beforehand to keep log growth manageable.
-E needs ALTER TABLE on the destination, which is more than the SELECT and INSERT a plain import wants. The documentation says a bcp in “minimally requires SELECT and INSERT permissions on the target table,” and lists ALTER TABLE as additionally required when “you use the -E option to import identity values from a data file.” That didn’t come up for me because I had rights on the target anyway, but it’s the kind of thing that would stop you at the worst moment if you were working with a least-privilege account on someone else’s server. The same list has two other ways to end up needing ALTER TABLE, which are constraints on the table when you haven’t passed CHECK_CONSTRAINTS, and triggers when you haven’t passed FIRE_TRIGGERS, so a plain import can want it even without -E.
bcp AdventureWorks2022_dest.Person.Person in person_2013.bcp -S localhost -T -f person.fmt -u -E -b 10000 -e person_errors.txt -q
Some guides recommend -h TABLOCK to reduce lock overhead during bulk inserts. In my case the database wasn’t actively being used during the migration, so it didn’t matter either way.
The -b 10000 is the flag I’d have skipped if I hadn’t read what it does. Without it bcp imports the whole file as a single transaction, so a failure on the last row rolls back everything. With a batch size, “if the transaction for any batch fails, only insertions from the current batch are rolled back”, and the batches that already committed stay put. On 27,000 rows that’s not a big deal. On a few million it’s the difference between resuming and starting over.
After the import I ran SELECT COUNT(*) with the same WHERE clause on both servers to confirm the numbers matched, checked the error file was empty, and ran UPDATE STATISTICS on the destination table.
That’s pretty much it! I don’t have to do this very often but it’s nice to have in the toolkit for when I do. The part I’d want to test before trusting it at a larger scale is what resuming actually looks like, since I’ve never had a batch fail partway and had to work out which rows made it and which didn’t.
Flags I Used
| Flag | What it does |
|---|---|
-S |
Server and instance name |
-T |
Windows integrated auth |
-n |
Native binary format – only needed when generating the format file; omit it when using -f or bcp will warn that -f overrides it |
-f |
Path to the format file |
-E |
Preserve identity values from the file instead of generating new ones |
-b |
Batch size in rows, so each batch commits independently |
-e |
File to write rejected rows and error reasons to |
-h |
Table hints passed to the bulk insert operation |
-q |
Executes SET QUOTED_IDENTIFIER ON – required when the target table has filtered indexes, indexed views, or computed columns |
-u |
Trust the server certificate without validating the chain |
Full flag reference in the bcp documentation.