Does simple recovery model not log bulk inserts?

Question

I have a db in full recovery model.

There is a planned insert of a million rows that needs to be done next week.

In order to not increase the log size, I am told there are 2 options.

Option 1 is switch to bulk model, perform the insert and then switch to full model. This makes perfect sense.

Option 2 is switch to simple recovery model, perform the insert and then switch to full model. Is this really true? Does simple recovery model not increase the log size during bulk insert? I know that log is cleared at each checkpoint, but during bulk insert does the checkpoint occur?

Answer 1

INSERT can be minimally logged in some cases. See: Prerequisites for Minimal Logging in Bulk Import.

Note if you switch to SIMPLE recovery your log chain is broken, and you'll need to start with a new full backup after switching back to the FULL recovery model.

If you switch to BULK_LOGGED then inserted pages are copied into the next log backup. Switching to BULK_LOGGED does not break the chain — that's why it exists.

See Bulk Import Optimizations (Minimal Logging) by Sunil Agarwal for more details. Some quotes:

Under FULL recovery model, as the name implies, all operations are fully logged. Under minimal logging, the SQL Server does not log individual rows and logs only the page and extent allocations. So if each page can fit 100 rows, we are trading 100 log records for 1 log record, a significant improvement.

When the bulk import transaction commits ... the SQL Server flushes all the data pages (a subset of these pages are likely to have been flushed as part of checkpoint) to disk to guarantee the atomicity of the transaction.

When the [next] transaction log backup is taken, the SQL Server also backs up the data pages, including index pages when applicable, as part of the transaction log. This enables a database restore by restoring the database backup followed by zero or more transaction log backups. The only drawback of minimal logging is that it does not support point-in-time recovery ... If this is of concern, you can do transaction log backup before/after bulk operations.

So you typically switch back to FULL recovery model, and take a log backup immediately after performing the minimally logged operation.