INF: BCP and Transaction Logging (46356)

This article was previously published under Q46356

This article discusses the use of BCP (bulk copy program) as it applies to the transaction log.

If BCP IN is run on a table without an index, the inserts are not logged; however, the only way BCP can be run on such a table is to set the SELECT INTO/BULKCOPY dboption.

If a BCP IN is run on a table with an index, the inserts are logged (and the index is populated), regardless of the setting of the SELECT INTO/BULKCOPY dboption.

It is important to note that transaction logging is never turned off completely. All page and extent allocations must be logged. This is needed to roll back the BCP operation in case of failure.

Note that SELECT INTO/BULKCOPY cannot be set in the master database; that is, only indexed tables can be loaded with BCP in the master database.

After the BCP IN operation is complete, it is strongly suggested that a backup of the database be performed to preserve all of the new data that has been loaded into the database.