DIFFERENCE BETWEEN FULL and BULK Recovery Models in SQL Server

DIFFERENCE BETWEEN FULL and BULK Recovery Models in SQL Server :

Bulk logged operations are minimally logged in case of bulk logged recovery and fully logged in case of full recovery model.
In bulk logged recovery, t-log file will not grow much as compare to full recovery for bulk logged operation.

In bulk logged recovery, if t-log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup.

Example: if you have a tlog backup that contains transactions between 1:00 am to 2:00 am, and you have a bulk logged operation starting at 1:00 am and completes at 1:20 am (20 minutes). You can not recover your database at time 1:10 am in case of bulk logged recovery but you can in case of full recovery.

Simple Recovery Model

In databases using the simple recovery model, you may restore full or differential backups only. It is not possible to restore such a database to a given point in time, you may only restore it to the exact time when a full or differential backup occurred. Therefore, you will automatically lose any data modifications made between the time of the most recent full/differential backup and the time of the failure.

Full Recovery Model

In the event of a database failure, you have the most flexibility restoring databases using the full recovery model. In addition to preserving data modifications stored in the transaction log, the full recovery model allows you to restore a database to a specific point in time. For example, if an erroneous modification corrupted your data at 2:36AM on Monday, you could use SQL Server’s point-in-time restore to roll your database back to 2:35AM, wiping out the effects of the error. 

Bulk-logged Recovery Model

The bulk-logged recovery model is a special-purpose model that works in a similar manner to the full recovery model. The only difference is in the way it handles bulk data modification operations. The bulk-logged model records these operations in the transaction log using a technical known as minimal logging. This saves significantly on processing time, but prevents you from using the point-in-time restore option.

Microsoft recommends that the bulk-logged recovery model only be used for short periods of time. Best practice dictates that you switch a database to the bulk-logged recovery model immediately before conducting bulk operations and restore it to the full recovery model when those operations complete.

BULK LOGGED recovery Model:

First of all there is difference between NO LOGGED and MINIMALLY LOGGED. In Bulk Logged Recovery model, bulk operations are minimally logged and not fully logged. This does not mean that bulk operations are never logged in transaction log.

Here are the operations which get minimally logged when you database is in bulk logged recovery model:

·         Bulk import operations (bcp, BULK INSERT, and INSERT... SELECT).

·         SELECT INTO operations

·         WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns

·         Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data

·         CREATE INDEX operations (including indexed views).

·         ALTER INDEX REBUILD or DBCC DBREINDEX operations.

Actually in bulk logged recovery model, allocation of bulk operations are stored in transaction log and not complete data page. Since data pages are not getting logged in the transaction log, this recovery model does not take much space in the log. And this is the reason transaction log does not grow when we do costly bulk operations especially rebuilding the indexes. But when we take the transaction log backup, log backup will have complete data pages as well to provide you the ability to restore bulk operations. This is the reason of having bigger size of transaction log backup compare to transaction log file sometimes. Here the diagram which can give clear picture of this concept.

Data files are the one which basically holds the data pages of the bulk insert operations. This is the reason if a minimally-logged operation has been performed since the last log backup, and one or more data files were damaged and offline because of the disaster, a tail-of-the-log backup cannot be performed and so all user transactions performed since the last log backup will be lost.

Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions.

  advantage and disadvantage of this recovery model in SQL server.

Advantage:

Since bulk operations are minimally logged, this increases the performance of the bulk operations. Also it does not allow the log to grow unexpectedly when we do some costly operations like rebuild index, create index etc.

Disadvantage:

If transaction log is damaged, changes since the last backup must be redone. Also if any bulk operations occurred since the most recent log backup, changes since the last backup must be redone.

Remember:

1.    You cannot use point in time restore option if your database is set to bulk logged recovery model.

2.    You have to take the transaction log backups else your log will not be truncated.

3.    If you change recovery model to bulk logged from Full then you do not disturb the log backup chain. But it is always a very good practice to take the log backup before and after making changes of recovery model.

4.    Microsoft recommends using this recovery model when and only when we do bulk operations (Mentioned above). Once the operation(s) is/are completed, switch back to FULL recovery model so that your database can be restored to point in time if disaster happens.

Hope this helps you to understand the concept of BULK LOGGED RECOVERY model in SQL server. I feel this recovery model very important for the databases where we do not need point in time recovery and we have so many bulk operations running.

 The advantage of using the "Bulk-logged" recovery model is that your transaction logs will not get that large if you are doing bulk operations and it still allows you to do point in time recovery as long as your last transaction log backup does not include a bulk operation as mentioned above.  If no bulk operations are run this recovery model works the same as the Full recovery model.  One thing to note is that if you use this recovery model you also need to issue transaction log backups otherwise your database transaction log will continue to grow.


Here are some reasons why you may choose this recovery model:

• Data is critical, but you do not want to log large bulk operations
• Bulk operations are done at different times versus normal processing.
• You still want to be able to recover to a point in time

Type of backups you can run when the data is in the "Bulk-logged" recovery model:

• Complete backups
• Differential backups
• File and/or Filegroup backups
• Partial backups
• Copy-Only backups
• Transaction log backups

Set bulk-logged recovery model using T-SQL

ALTER DATABASE dbName SET RECOVERY recoveryOption
GO

Example: change AdventureWorks database to "Bulk-logged" recovery model

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED
GO