Essentially the Bulk Logged recovery model is the same as the Full recovery model except that some operations are minimally logged. As long as none of those operations happen, you effectively have a database in the Full recovery model.

I did in fact perform some fairly extensive tests before posting this question, using SELECT INTO as my minimally logged operation. As long as it wasn't in the relevant backup I could restore to a point in time. I'm pasting my code below for anyone who wants to try for themselves.

This first bit of code creates the database, a table and inserts some records. It does a bulk operation, performs a log backup, adds some more records then drops the table.

-- Create our database

CREATE DATABASE DisasterRecoveryDemo;

GO

-- Make sure we're in bulk-logged recovery mode

ALTER DATABASE DisasterRecoveryDemo

SET RECOVERY BULK_LOGGED;

GO

USE DisasterRecoveryDemo;

GO

-- Create a table for our test data

CREATE TABLE ImportantData

(

ID int,

EntryDateTime datetime

);

GO

-- Insert some initial values

INSERT INTO ImportantData (ID, EntryDateTime)

VALUES(1, GETDATE()), (2,GETDATE()), (3, GETDATE());

GO

-- Perform a full database backup

BACKUP DATABASE DisasterRecoveryDemo TO DISK = 'DisasterRecoveryDemo_Full.bak';

GO

-- Perform bulk-logged operation

SELECT ID, EntryDateTime

INTO ImportantData2

FROM ImportantData;

GO

-- Perform a log backup. Now the just-inserted record will be backed up

BACKUP LOG DisasterRecoveryDemo TO DISK = 'DisasterRecoveryDemo_Log.bak';

GO

INSERT INTO ImportantData2 (ID, EntryDateTime)

VALUES(4, GETDATE()), (5,GETDATE()), (6, GETDATE());

GO

-- Make a mental note of the contents of the table

SELECT * FROM ImportantData2;

GO

-- Check the current time

SELECT GETDATE();

GO

-- If you wait for a bit you can do a simulated disaster, e.g.

WAITFOR DELAY '00:00:10';

GO

DROP TABLE ImportantData2;

GO

Making a note of the time returned by the GETDATE statement, you can run the following code to get the database back to just before you dropped the table

USE master

GO

-- Set the database to single user so that no other connections are allowed

ALTER DATABASE DisasterRecoveryDemo

SET SINGLE_USER;

GO

-- Back up the remainder of the log using NORECOVERY mode - called a tail log backup

BACKUP LOG DisasterRecoveryDemo TO DISK = 'DisasterRecoveryDemo_TailLog.bak'

WITH NORECOVERY;

GO

-- Begin the restore process with restoring the full backup

RESTORE DATABASE DisasterRecoveryDemo FROM DISK = 'DisasterRecoveryDemo_Full.bak'

WITH NORECOVERY;

GO

-- Next restore the first log backup

RESTORE LOG DisasterRecoveryDemo FROM DISK = 'DisasterRecoveryDemo_Log.bak'

WITH NORECOVERY;

GO

-- Finally restore the tail log backup, stopping at just before the disaster

RESTORE LOG DisasterRecoveryDemo FROM DISK = 'DisasterRecoveryDemo_TailLog.bak'

WITH RECOVERY, STOPAT = '<time noted earlier>';

GO

-- Get the database back into multi user mode

ALTER DATABASE DisasterRecoveryDemo

SET MULTI_USER;

GO

USE DisasterRecoveryDemo;

GO

-- Check the data is restored

SELECT * FROM ImportantData2;

And to clean up

USE master

GO

ALTER DATABASE DisasterRecoveryDemo

SET SINGLE_USER;

GO

DROP DATABASE DisasterRecoveryDemo;

GO

By moving the position of the SELECT INTO statement, you can see that point in time recovery is not possible if it occurs in the backup you use to restore to a point in time and you get this error:

Msg 4341, Level 16, State 1, Line 2

This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.

Hope that helps,

Duncan