If the database option select into/bulkcopy is set to true, Microsoft® SQL Server™ does not record the full details of the following operations in the transaction log:
While these operations are known as nonlogged operations, certain changes from the operations are always logged. These operations all allocate one or more new pages to hold the data they add. The page allocations are logged, but the details of the data added in the pages is not. SQL Server can roll back these nonlogged operations by reversing the page allocations recorded in the log. SQL Server cannot roll forward the operations because the log has no record of the data that would have to be added in the new pages.
Because a nonlogged operation cannot be rolled forward, these operations invalidate a log backup sequence. A full or incremental database backup must be performed after a nonlogged operation to start a new log backup sequence. Therefore, if one of these operations occurs, SQL Server allows the transaction log to be backed up but warns that the database should be backed up first, thereby preserving the changes in the event that the database must be restored.
Backing Up Nonlogged Operations | Logged and Nonlogged Bulk Copy Operations |
Creating and Applying Transaction Log Backups | Setting Database Options |