Koen Verbeeck (2/16/2011)
Shawn Melton (2/16/2011)
I call shenanigans. How can the answer not be no? If a database is set to Bulk-Logged recovery model that means some transactions are minimally logged. That would mean the complete transaction would not exist in thedatabaselog file, how can you restore to that when SQL cannot rebuild the whole transaction?The correct answer stated that you could restore to a point in time if there were no bulk-logged changes.
BOL however contradicts itself. In one page they say it isn't possible, on another they say it can be done if no bulk logged operations have occurred.
Even the references that you gave contradict themselves.
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