Full disclosure time: Bulk Logged Recovery Model is quite confusing to me. And as it seems, to many others. I wrote a bit about it in SQL Server Recovery Models and decided that it was so complex, I really wanted to learn more and to explore what works and what doesn’t. Let’s take a deep dive into bulk logged recovery!
Why would you choose Bulk Logged Recovery?
Switching from full recovery to bulk logged recovery does have its perks when you have a very large amount of data to insert. Most notably in a data warehouse setting, switching to bulk logged recovery to perform bulk inserts make perfect sense as you are dealing with very large amounts of data being updated at one time. Also, when doing an index rebuild switching to bulk logged recovery can improve performance while performing operations on large amounts of data at once.
Are there better ways to insert a large amount of data at once?
Bulk Logged Recovery uses minimal logging for bulk-logged operations, this reduces log space usage. I must add a caveat here; it makes it faster and reduces the usage in the file, but it results in a very large log backup.
“Under the bulk-logged recovery model, if a log backup covers any bulk operations, the log backup contains both log records and the data pages that were changed by bulk operations. This is necessary to capture the results of the bulk-logged operations. The incorporated data extents can make a log backup very large.” Reference Myths & Truths
However, there is a risk of data loss for the bulk operations because these operations prevent capturing changes on a transactional basis. A point in time recovery (PIT) while using bulk logged recovery is not possible because the minimally logged operations cannot be restored. This can be an issue. So, if you have a bulk operation that needs to be handled, but you want to ensure point in time restore of each transaction in that operation, what is an alternative solution? It is important to note that you can indeed recover a transaction log containing bulk logged operations but not to a particular point in time. Instead you can take a transaction log backup as soon as the bulk operation is finished and regain PIT recovery.
You can still perform bulk operations in full recovery model, it just means that they will be fully logged and that you will not see the performance gains from minimal logging. It is the price you have to pay, you sacrifice performance for PIT restore abilities of the transactions within the bulk operation. Ultimately your decision will have to be based on weighing what your company demands for I/O and RPO/RTO (Recovery Point Objective/Recovery Time Objective). Do you know what your company’s RPO/RTO plans entail? Now is a good time to find out!
Feeling uneasy? Wondering if there are there other ways to process bulk operations?
There are different methods one can utilize for optimizing bulk operations. These methods include using minimal logging, batching, disabling triggers and constraints, and many others that can be found here.
How best to ensure limited data loss using Bulk Logged Recovery.
So you decide you are going to use bulk logged recovery and you want to make sure that you are set up for success, there are a few things to keep in mind. It is recommended that you perform bulk inserts using bulk logged recovery when there is the least amount of activity on your database. Also take into consideration how difficult or easy it will be to recreate data if there is a failure during the bulk insert. There is no PIT restore of the bulk operation using bulk logged recovery. If the bulk operation is interrupted at any point, the entire operation must be performed again in its entirety.
Still want to proceed?
First, before you switch from full recovery, take an extra log backup. If all things go badly, at least you will be able to get your database back to the point before you switch recovery models. This is highly recommended! If not, this is what we call an RGE (resume generating event).
Let’s walk through the process of getting ready and switching recovery models. Our first step in this exercise is to create a table. We then go on to taking a log backup, inserting data manually, taking a log backup, and then on to switching to bulk logged recovery.
This is a great visual of what we will be doing.
--Step 1--
Use BLRDB
GO
DROP TABLE dbo.BulkDataTest;
CREATE TABLE dbo.BulkDataTest
(Price money NULL,
ProductID int PRIMARY KEY NOT NULL,
ProductName varchar (25) NOT NULL,
ProductDescription text NULL)
GO
BACKUP DATABASE LOG [BLRDB]
TO DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0800.bak'
-- Step 2--
Insert into dbo.BulkDataTest
(Price, ProductID, ProductName, ProductDescription)
VALUES ('456', '456123', 'HeroBike', 'Red Bike with Hero Cape Handles');
-- Step 3 --
BACKUP LOG [BLRDB]
TO DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0815.bak'
-- Step 4 --
-- Switch to Bulk Logged Recovery Model --
USE [master]
GO
ALTER DATABASE [BLRDB] SET RECOVERY BULK_LOGGED WITH NO_WAIT
GO
-- verify Recovery mode This script will ensure that you are in the desired recovery model--
select name, recovery_model_desc from sys.databases
Our next steps will be to insert our bulk data, insert manual data, take log backups, switch back to Full Recovery, and take an additional log backup.
-- Step 5 --
-- Commit both transactions below at the same time--
USE [BLRDB]
GO
BULK INSERT BulkDataTest
FROM 'C:\DBA\TestDocs\demo_bulk_insert_26.csv'
With (FIELDTERMINATOR = ',' ,
ROWTERMINATOR = '\n' ,
ROWS_PER_BATCH = 100000,
TABLOCK
);
GO
INSERT INTO BulkDataTest
(Price, ProductID, ProductName, ProductDescription)
VALUES ('1099', '1111111', 'HoverCraft', 'BippityBoppityBoop');
Select *
From dbo.BulkDataTest
-- Step 6 --
--take log backup--
BACKUP LOG [BLRDB]
TO DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0900.bak'
-- Step 7 --
--Insert more rows manually--
INSERT INTO dbo.BulkDataTest
(Price, ProductID, ProductName, ProductDescription)
VALUES ('56', '111117', 'TheCheap', 'One of the cheapest bikes ever made'),
('58' , '111118', 'NewerModel', 'This one is for beginners'),
('591' , '111119', 'ABetterOne', 'Okay this one is good') ;
-- Step 8 --
-- Switch back to Full Recovery Mode--
USE [master]
GO
ALTER DATABASE [BLRDB] SET RECOVERY FULL WITH NO_WAIT
GO
Use BLRDB
GO
-- Step 9 --
--Insert more rows manually--
INSERT INTO dbo.BulkDataTest
(Price, ProductID, ProductName, ProductDescription)
VALUES ('36', '111120', 'BoyBike', 'This is a bike for tall 8yo'),
('136', '111121', 'ManBike', 'This is a bike for tall men'),
('236', '111122', 'ShortBike', 'This is a bike for under 5foot');
-- Step 10 --
--Take Log Backup--
BACKUP LOG [BLRDB]
TO DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0930.bak'
Ready for a challenge?
Now let’s simulate a dropped database and walk through restore! The following steps will walk you through to be able to answer the questions below.
Question 1: Restore backup to step 6. What is missing? Do you have the single row inserts? Is the data from the bulk insert there?
--Drop Database--
USE Master
GO
DROP DATABASE [BLRDB]
--Restore Full Backup --
RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Full_02232018.bak'
WITH NORECOVERY;
RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0800.bak'
WITH NORECOVERY;
RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0815.bak'
WITH NORECOVERY;
RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0900.bak'
WITH NORECOVERY;
RESTORE DATABASE BLRDB
WITH RECOVERY;
use [BLRDB]
go
SELECT *
FROM dbo.BulkDataTest
--For Qustion 1, Restore Step 6 --
USE Master
GO
RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Full_02232018.bak'
WITH NORECOVERY;
GO
RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0800.bak'
WITH NORECOVERY;
GO
RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0930.bak'
WITH NORECOVERY;
GO
RESTORE DATABASE BLRDB WITH RECOVERY;
GO
use [BLRDB]
go
SELECT *
FROM dbo.BulkDataTest
Question 2: Restore to backup at step 10. What is missing? Do you have everything?
-- For Question 2, Restore Step 10 --
-- Drop DB--
USE Master
GO
DROP DATABASE [BLRDB]
GO
USE Master
GO
RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Full_02232018.bak'
WITH NORECOVERY;
GO
RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0800.bak'
WITH NORECOVERY;
GO
Restore Database BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0815.bak'
WITH NORECOVERY;
GO
Restore Database BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0900.bak'
WITH NORECOVERY;
GO
Restore Database BLRDB
FROM DISK = 'C:\DBA\Backups\BLRDB_Log_02232018_0930.bak'
WITH RECOVERY;
USE BLRDB
GO
SELECT *
FROM dbo.BulkDataTest
Swimming back up. 
We have taken the plunge and now it is time to swim back to the surface and discuss what we have found. Working through this exercise and answering these questions should show you how bulk logged recovery model works, why it is important to switch back to full recovery, and most importantly, why full and log backups are a must!
What are your answers? Your answers should show that if you are only able to restore to step 6 (the point at which you took a log backup but had not yet switched back to full recovery and taken another log backup) all data operations that were performed since your bulk operation have to be recreated! The second answer should show you that it is imperative to take a log backup before switching to bulk recovery, take a log backup after your bulk insert, and take another log backup after reverting back to full Recovery. If you follow those steps, your answer shows that all of your data operations were in fact logged and can be restored up to the last log backup without data loss.
