T-SQL Tuesday #31 – Bulk-Logged Recovery Model and Point-in-time Restore

This blog entry is participating in T-SQL Tuesday #31, hosted this month by Aaron Nelson (Blog|@SQLVariant). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: Logging.

Clark Kinsey Photograph Collection. University of Washington Libraries. Special Collections Division

For my part in this shindig known as T-SQL Tuesday, I’m going to write about the bulk-logged recovery model and point-in-time restore. When choosing a recovery model for a database it is important to know what you are giving up when you elect to use a recovery model other than full. I want to investigate one of those points in particular, point-in-time restore.

One of the things I hear often about using the bulk-logged recovery model is that you lose the ability to perform point-in-time restores. I want to do a little investigating to either prove or debunk that belief.

Point-in-time Restore

One of the caveats of point-in-time-restores is that the time must be within the range of the log file being used. Books Online has this to say about point-in-time recovery when using the bulk-logged recovery model:

If a log backup taken under the bulk-logged recovery model contains bulk-logged changes, point-in-time recovery is not allowed. Trying to perform point-in-time recovery on a log backup that contains bulk changes will cause the restore operation to fail.

Let’s test this theory. The first thing to do is create a database for the tests and change the recovery model to bulk-logged recovery model. Then we take a full backup to initialize the log chain.

Use master;
-- Drop database if it exists
If DB_ID('BulkLoggedDB') Is not Null
    Drop Database BulkLoggedDB;
GO
-- Create new database, set to bulk-logged recovery, and take a full backup
Create Database BulkLoggedDB;
GO
Alter Database BulkLoggedDB Set Recovery Bulk_logged;
Go
Backup Database BulkLoggedDB
    To Disk = 'C:\bak\BulkLoggedDB.bak'
    With init;
Go

The next step is to perform a series of transactions and log backups. The end result is to wind up with two log backups. The first log backup contains 2 transactions, neither of which are minimally logged. We capture a time point between the two transactions so we can verify if point-in-time restore works. The second log backup contains another two transactions. The first transaction is not minimally logged and the second transaction is minimally logged (SELECT … INTO is minimally logged). Again we capture a time point between the two transactins to use for the restore test.

-- Switch to BulkLoggedDB, add some data, wait a while, and add more data
Use BulkLoggedDB;
-- Create and populate a table in non-minimally logged transaction
Create Table dbo.AllDatabases (
    DBID int not null primary key,
    DBName sysname not null)
Go
Insert Into dbo.AllDatabases
Select database_id, name
From sys.databases;
Go
-- Pause for 2 minutes
Waitfor Delay '0:02:00.000';
-- Capture the time
Select GETDATE() As TimeBeforeInsert2;
-- Insert more data in a non-minimally logged transaction
Insert Into dbo.AllDatabases
Select database_id + 1000, name
From sys.databases;
Go
-- Backup the log
Backup Log BulkLoggedDB
    To Disk = 'C:\bak\BulkLoggedDB_NoMLTrans.trn'
    With init;
Go
-- Create and populate a table in non-minimally logged transaction
Create Table dbo.AllDatabases2 (
    DBID int not null primary key,
    DBName sysname not null)
Go
Insert Into dbo.AllDatabases2
Select database_id, name
From sys.databases;
Go
-- Pause for 2 minutes
Waitfor Delay '0:02:00.000';
-- Capture the time
Select GETDATE() As TimeBeforeInsert2;
GO
-- Perform a minimally-logged transaction
Select * Into dbo.Alldatabases3
From sys.databases
Go
-- Backup the log
Backup Log BulkLoggedDB
    To Disk = 'C:\bak\BulkLoggedDB_WithMLTrans.trn'
    With init;
Go

Now for the test. First we’ll attempt a point-in-time restore with the first log backup specifying the first date/time output with the above step. It should be successful despite being in bulk-logged recovery model because there were no minimally logged transactions in the log backup.

-- Switch to master and attempt point-in-time restores
Use master;
Go
-- Restore full backup
Restore Database BulkLoggedDB
    From Disk = 'c:\bak\BulkLoggedDB.bak'
    With Replace, Norecovery;
Go
-- Restore first log backup stopping at the time before the 2nd transaction
-- Set the StopAt time to the first date/time output above
Restore Log BulkLoggedDB
    From Disk = 'c:\bak\BulkLoggedDB_NoMLTrans.trn'
    With Replace, Recovery,
        StopAt = '2012-06-12 14:29:41.343';
Go
-- Restore completed without error.
-- Query dbo.AllDatabases to see if it stopped at the specified time or continued to end fo the backup
Select *
From BulkLoggedDB.dbo.AllDatabases
Order by DBName;

Data from the second insert i=was not present, so the point-in-time restore was successful. Now, let’s try the 2nd log backup that has a minimally-logged transaction in it. But first, let’s prove that the 2nd log backup has a minimally logged transaction.

-- Read the backup header and look at the BulkLoggedData column
Restore HeaderOnly From Disk = 'C:\bak\BulkLoggedDB_WithMLTrans.trn';
Go
-- Restore full backup
Restore Database BulkLoggedDB
    From Disk = 'c:\bak\BulkLoggedDB.bak'
    With Replace, Norecovery;
Go
-- Restore first log backup stopping at the time before the 2nd transaction
-- Set the StopAt time to the first date/time output above
Restore Log BulkLoggedDB
    From Disk = 'c:\bak\BulkLoggedDB_NoMLTrans.trn'
    With Replace, NoRecovery;
Go
-- Restore second log backup stopping at the time before the 2nd transaction
-- Set the StopAt time to the 2nd date/time output above
Restore Log BulkLoggedDB
    From Disk = 'c:\bak\BulkLoggedDB_WithMLTrans.trn'
    With Replace, Recovery,
        StopAt = '2012-06-12 15:01:09.353';
Go

In the second test, we get an error about there being a minimally logged transaction in the log backup. We’re not able to perform a point-in-time restore with this log backup even though we are telling it to stop before the minimally logged transaction occurred.

The error message:

Msg 4341, Level 16, State 1, Line 4
This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.
Msg 4338, Level 16, State 1, Line 4
The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.
Msg 3013, Level 16, State 1, Line 4
RESTORE LOG is terminating abnormally.

Summary

Using bulk-logged recovery is not enough to prevent the usage of point-in-time recovery. As long as there are no minimally logged transactions in that log backup, you are still able to perform a point-in-time restore.

T-SQL Tuesday #31 – Bulk-Logged Recovery Model and Point-in-time Restore