Logged and Nonlogged Bulk Copies
Microsoft® SQL Server™ bulk copies that import data into an instance of SQL Server are run in either logged or nonlogged mode. The difference between logged and nonlogged bulk copy operations is how much information is logged. Both logged and nonlogged bulk copy operations can be rolled back, but only a logged bulk copy operation can be rolled forward.
In a logged bulk copy all row insertions are logged, which can generate many log records in a large bulk copy operation. These log records can be used to both roll forward and roll back the logged bulk copy operation. In a nonlogged bulk copy, only the allocations of new pages to hold the bulk copied rows are logged. This significantly reduces the amount of logging that is needed and speeds the bulk copy operation. If a nonlogged bulk copy operation encounters an error and has to be rolled back, the allocation log records are used to deallocate the pages holding the bulk copied rows. Since the individual row insertions are not logged in a nonlogged bulk copy, however, there is no log record of the individual rows that could be used to roll forward nonlogged bulk copy operations. This is why a nonlogged bulk copy operation invalidates a log backup sequence.
If the database option trunc. log on chkpt. is set on, then there is no need to generate log records that would support rolling forward a bulk copy operation. Use nonlogged bulk copy operations in databases where trunc. log on chkpt. is turned on.
Whether a bulk copy is logged or nonlogged is not specified as part of the bulk copy operation; it is dependent on the state of the database and the table involved in the bulk copy. A nonlogged bulk copy occurs if all the following conditions are met:
- The database option select into/bulkcopy is set to true.
- The target table has no indexes, or if the table has indexes, it is empty when the bulk copy starts.
- The target table is not being replicated.
- The TABLOCK hint is specified using bcp_control with eOption set to BCPHINTS.
Any bulk copy into SQL Server that does not meet these conditions is logged.