5

What is the difference between bulk_logged and simple recovery model?

Which one is minimally logged and what is always a better choice for bulk operations?

suppose i am using select into statement to copy more than millions of rows, which one is optimal to use?

1 Answers1

3

The Bulk Logged Recovery model is almost identical to Full, but the Bulk Logged model will not log INSERT/UPDATE specifics - only the table extents. Simple Recovery Model is the only one that does not require log file maintenance, but its log files can not be used to restore data in the event of loss.

Of the Recovery Models, Simple is the most minimally logged and Bulk Logged is recommended for bulk operations.

Reference: Recovery Model Overview

SELECT INTO is preferred, but that means that data already exists and is accessible.

OMG Ponies
  • 396
  • 1
  • 7
  • Which one is to be used in my case if no data recovery is needed? Only concern is that the operation must not fail due to log file getting full. –  Sep 26 '09 at 20:15
  • @Aamod: You want to use **Simple** if no data recovery is needed and don't need/want to maintain logs. – OMG Ponies Sep 26 '09 at 20:25