BCP – An Old Standby

Featured White Paper(s)
Free Poster – SQL Server Perfmon Counters of Interest
When you’re looking for a reliable tool to diagnose SQL Server issues, Perfmon is often the answer. No more wasting time trac… (read more)

Featured Script
Oracle: Howto: Rename or Move data files with database open
Datafiles can be renamed or moved while the database is open. However, the tablespace must be made READ-ONLY. This will al… (read more)

BCP – An Old Standby
Recently I needed to copy some data from one of my production servers to a development instance on a completely different server, allowing me to do some unit testing. I fired up the data export wizard in SQL Server Management Studio, filled in all the parameters, including selecting source and destination servers and databases, clicked Run now, and watched it……stop. Error, invalid credentials.

Not to get into a long drawn out story; but the two environments are set up not to communicate with each other intentionally. I could build a tunnel or some other mechanism to get the data pipeline to work, or put together an SSIS package. But I grew up in the Old School when we didn’t even have DTS (SSIS Predecessor). We either wrote queries to extract data in the form of an Insert Script (really old school) or with Sybase and later SQL Server, you had the command line too for Bulk Copy in the form of BCP.EXE. And, it still works today.

I don’t often use the old BCP command line because of the many rich tools available. However, in this case, BCP was a nice little tool.

I opened a DOS command prompt and entered BCP /? [Enter] because I wanted a refresher on the syntax. I didn’t even have to enter the path for BCP because SQL Server modifies the Path variable for you when it installs the SQL Server tools. You just have to know the dos command BCP.

I was now able to type in a BCP command. Using aliases, here is what I ended up with…

BCP dbo.MyTable OUT d:temptransfer.bin -S productionServer -d MyDatabase -T -n

DEL D:temptransfer.bin

I’m done. And, it’s lightening fast.

Here’s the parameters I chose:

BCP [Table|View] Direction [in|out] [Destination File Name]
-S [SQL Instance]
-d [Database Name]
-T = Use Windows Credentials (There are parameters in the help if you have to use SQL Credentials)
-n = Use Native Binary File Format for exporting the data to or importing the data from

There’s a lot more you can do, but I would probably go to another tool for the more complicated stuff. There is little you can’t do with a BCP command line, but it can get pretty complicated. PowerShell has a nice BCP wrapper built in I would recommend if you need something more complicated than this, or want to be able to apply parameters to a re-usable script. Here I just needed to move data quick and easy. It’s pretty hard to get any easier than this.

Do you have other techniques you would like to share with our readers? Drop an email with your ideas to me at btaylor@sswug.org.

Cheers,

Ben