What gets logged in Bulk logged recovery model

Question

In Bulk logged recovery model, what gets logged- page allocations or extents allocations?
Suppose I inserted 2 million records into a table t1 using INSERT INTO. If i take full backup and restore it, will the restored database contain both t1 and the data?

Answer 1

But in bulk logged recovery model, data inserts are not logged , right? So how will the restored database contain data?

Well, you asked about a full backup, so there is not even any relation to what is being logged in the transaction log.

But to address your concern. let's say that you do this:

1. Take a full backup.
2. Create your table and add data to it in a minimally logged operation.
3. Take a transaction log backup.
4. Restore the full backup with NORECOVERY.
5. Restore the transaction log backup. (WITH RECOVERY this time).

Will the data in the table be there after the restore? The answer is yes. This because in step 3, all those pages in these extents for which SQL Server only logged the allocation of to the transaction log are written to the backup. SQL Server knows which pages this is, because there is a special bitmap page with this information.

The one thing you cannot do is to restore the transaction log and say "Hey, SQL Server stop at this point, just before I inserted the data". When you are in bulk_logged recovery, you need to restore transaction-log backups in whole, there is no point-in-time recovery.

Overall, there is never a situation where you take a backup (full, differenital, or log), and restore the backups, and you don't have the same data as you had originally. That would be a major bug. (OK, so there is one exception: there is corruption due to bad hardware somewhere along the line.)

Answer 2

In Bulk logged recovery model, what gets logged- page allocations or extents allocations?

Extent allocations.

Suppose I inserted 2 million records into a table t1 using INSERT INTO. If i take full backup and restore it, will the restored database contain both t1 and the data?

Yes.

Answer 3

Hi @Chaitanya Kiran ,

> Suppose I inserted 2 million records into a table t1 using INSERT INTO. If i take full backup and restore it, will the restored database contain both t1 and the data?

Yes. But if the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone. Such as below example; The data will lost from 14:00 -15:00, we need to redone it.

Though the bulk-logged recovery model reduces log space usage by using minimal logging for most bulk-logged operations, it is recommended to temporarily switch to bulk-logged recovery model right before performing these operations and then immediately switch back to the full recovery model.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".