Source: articles.techrepublic.com.com

Step-By-Step: How to move SQL Server data efficiently with the Bulk Copy Program

---

Takeaway: Learn to use the Bulk Copy Program (BCP), a command-line utility, to import and export large amounts of data in and out of SQL Server databases

---

The Bulk Copy Program (BCP) is a command-line utility that ships with Microsoft SQL Server. With BCP, you can import and export large amounts of data in and out of SQL Server databases quickly and easily. Any DBA who has utilized this functionality will agree that BCP is an essential tool.

BCP 101
You access the BCP utility from the command prompt. Here's the simple syntax:
bcp {dbtable | query} {in | out | queryout | format} datafile
[-n native type] [-c character type] [-S server name] [-U username]

[-P password] [-T trusted connection]

The command example I'm going to use starts with bcp followed by a fully qualified table name (database name, table or object owner, table or object name). For example, if you want to export the authors table, as part of the dbo group from the pubs database, you supply the full table name pubs.dbo.authors. Next, you use an in or out argument to specify whether you want BCP to copy data into or out of a database. You then specify the location of the datafile on your database server.

At this point, our BCP statement looks like this:
bcp pubs.dbo.authors out c:\temp\authors.bcp

---

Note
When using BCP, don't forget that the switches are case-sensitive. If you don't apply the right case, the BCP statement won't work correctly or will fail.

---

Next, we'll add some of the basic command-line options. The –n switch specifies native SQL Server format. The –c switch is used when formatting the file using Char as a datatype. If you use this switch, you can easily open your file with Excel. The –S switch enables you to add the server/instance name. The –U switch allows you to add the name of the login used to connect to SQL Server. The –P switch lets you add the password of the –U switch, and the –T switch is for establishing a trusted connection to your SQL Server. Figure A shows our completed BCP statement.

Figure A

Sample BCP out command

If we were importing data to the authors table, our BCP command would look like this:

bcp pubs.dbo.authors in c:\temp\authors.bcp -c -Sstevenw -Usa –P

That gives you an idea of the basics of the BCP utility, but there are plenty more switches at your disposal. In all, BCP supports 27 switches, which are shown in Listing A.

Listing A

bcp {dbtable | query} {in | out | queryout | format} datafile [-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n native type] [-c character type] [-w wide character type] [-N keep non-text native] [-V file format version] [-q quoted identifier] [-C code page specifier] [-t field terminator] [-r row terminator] [-i inputfile] [-o outfile] [-a packetsize] [-S server name] [-U username] [-P password] [-T trusted connection] [-v version] [-R regional enable] [-k keep null values] [-E keep identity values] [-h "load hints"]

With this many switches available, I recommend that you play around with them to see which ones you might want to use. For example, the –e switch is handy because it will create an error file you can look at if your BCP command returns errors. Here is an example:
bcp pubs.dbo.authors out c:\temp\steventest.txt -n -Sstevenw -Usa -P

-eC:\temp\error.txt

For more information on how to use the various switches, see SQL Server Books Online.

BCP and database objects

Let's create a new database called pubs2 and use BCP to move all the data from the pubs database into it. First, we need to create the database. Then, we'll generate the SQL script, run it on pubs2, and export the data from pubs into pubs2.

Start by creating the database in SQL Server Enterprise Manager and name it pubs2. Next, select pubs2, right-click on it, and choose Generate SQL Scripts from the All Tasks menu. Now you are ready to set your scripting options. Click Show All and then select the Script All Objects check box, as shown in Figure B.

Figure B

The Generate SQL Scripts dialog box

Go to the Options tab and select all the check boxes under Table Scripting Options, as we've done in Figure C. You can preview your script from the General tab or click OK to create and save the script.

Figure C

Table Scripting Options

---

Note
By performing the above, we are making a complete replica of the Pubs database. We could also have scripted out only one table or multiple tables. You can make the choice, depending on the task at hand.

---

Now, open Query Analyzer and run the newly created script (Figure D).

Run the script in SQL Query Analyzer.
After the script runs, the database objects and indexes will have been successfully created (Figure E).


The database objects have been created in SQL Server Enterprise Manager

To export the authors table out of pubs and import it into pubs2, we'll use the BCP statements we learned earlier:
bcp pubs.dbo.authors out c:\temp\pubauthors.bcp –n –Sstevenw –Usa –P

bcp pubs2.dbo.authors in c:\temp\pubauthors.bcp –n –Sstevenw –Usa –P

Then, open the SQL Query Analyzer and run the following statements on the pubs2 and pubs databases:
Use pubs2
Select * from authors
Select count(*) from authors

Use pubs
Select * from authors

Select count(*) from authors

This allows you to see the full process of moving data come full circle.

Summary
Most DBAs today use DTS, Database Restore, and/or attaching and detaching databases to copy data from one server to another. All these tools will get the job done. But if you want to copy large amounts of SQL Server data quickly, BCP is still one of the best tools available. Once you get past the initial learning curve, you'll find that BCP offers a highly efficient way to copy data from one database to another.