We use
Create a folder to contains backups
MD C:\Backups
CREATE DATABASE justdave2;
Take a full database backup
BACKUP DATABASE justdave2 TO DISK='C:\Backups\justdave2-full1.bak' WITH INIT,FORMAT;
We now create a table,insert a row and take a log backup
USE justdave2; CREATE TABLE job (client_id INT); -- First timestamp 2017-05-10 07:13:01.120 SELECT CURRENT_TIMESTAMP; WAITFOR DELAY '00:00:02'; BEGIN TRAN empty_table; INSERT INTO job VALUES(1); COMMIT TRAN; BACKUP LOG justdave2 TO DISK='C:\Backups\justdave2-log1.bak' WITH INIT,FORMAT;
Next in a second session we start a transaction and insert a row into the table to keep the transaction open
USE justdave2; BEGIN TRAN second_insert; INSERT INTO job VALUES(2); COMMIT TRAN; BEGIN TRAN third_insert; INSERT INTO job VALUES(3);
In the first session we check for open transactions and change to the to BULK LOGGED recovery model
DBCC OPENTRAN();
In the first session we change the database justdave2 to BULK LOGGED recovery model
ALTER DATABASE justdave2 SET RECOVERY BULK_LOGGED;
In the second session we now perform a minimally logged operation and commit
SELECT * INTO job2 FROM job; COMMIT TRAN; DBCC OPENTRAN();
In the second session we now perform a fully logged operation and commit
WAITFOR DELAY '00:00:02'; -- Second timestamp 2017-05-10 07:16:36.220 SELECT CURRENT_TIMESTAMP; BEGIN TRAN fourth_insert; INSERT INTO job VALUES(4); COMMIT TRAN; DBCC OPENTRAN();
In the first session we now backup the log again
BACKUP LOG justdave2 TO DISK='C:\Backups\justdave2-log2.bak' WITH INIT,FORMAT;
We restore to a point in time using the first log backup
-- In both sessions USE master; -- In the first session RESTORE DATABASE justdave2 FROM DISK='C:\Backups\justdave2-full1.bak' WITH NORECOVERY,REPLACE; -- Restore stopping at point in time at the first timestamp above, works fine. RESTORE LOG justdave2 FROM DISK='C:\Backups\justdave2-log1.bak' WITH STOPAT='2017/05/10 07:13:01', NORECOVERY; -- Bring the database online RESTORE DATABASE justdave2 WITH RECOVERY; USE justdave2; -- Get back 0 rows SELECT COUNT(*) FROM job;
We now try a point in time restore using the second timestamp
-- In the first session USE master; RESTORE DATABASE justdave2 FROM DISK='C:\Backups\justdave2-full1.bak' WITH NORECOVERY,REPLACE; RESTORE LOG justdave2 FROM DISK='C:\Backups\justdave2-log2.bak' WITH NORECOVERY; RESTORE LOG justdave2 FROM DISK='C:\Backups\justdave2-log2.bak' WITH STOPAT='2017/05/10 07:16:36', NORECOVERY; RESTORE DATABASE justdave2 WITH RECOVERY; USE justdave2; SELECT COUNT(*) FROM job;
This fails as the log backup contains minimally logged transactions
We use fn_dump_dblog to examine the log backup and see log entries which set bits in the ML (minimally logged) map.
As we know there was only 1 transaction running at a time we can tell the bits were set by transaction "third_insert"
SELECT [Begin Time],[Transaction ID],[Transaction Name],* FROM fn_dump_dblog(NULL,NULL,'DISK',1 ,'C:\Backups\justdave2-log2.bak' ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL) WHERE ( ([Operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT') ) OR ([Operation]='LOP_SET_BITS' and Context='LCX_ML_MAP') )
We can examine the log backup for just the entries which set bits in the ML map
SELECT [Begin Time],[Transaction ID],[Transaction Name],[Page ID],[Slot ID], [PartitionID],[Rowbits First Bit],[Rowbits Bit Count],[Description] FROM fn_dump_dblog(NULL,NULL,'DISK',1 ,'C:\Backups\justdave2-log2.bak' ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL) WHERE ([Operation]='LOP_SET_BITS' and Context='LCX_ML_MAP')
We can see
We confirm the database has been switch to bulk logged mode by the log restore
We can use DBCC PAGE to dump page 1:1 from the database and see the page 1:7 is an ML_MAP page
USE justdave2; -- Set output to the console DBCC TRACEON(3604); -- Dump the 1:1 page with just the page header DBCC PAGE (justdave2,1,1,0);
We can dump the page 1:7 in detail and see that after the restore no bits are set in the ML_MAP page
-- Dump the page header plus detailed per-row interpretation DBCC PAGE (justdave2,1,7,3);
We perform a new minimally logged operation and recheck the set bits in the ML_MAP page
We see that a bit has been set in the ML_MAP
SELECT * INTO job3 FROM job; -- Dump the page header plus detailed per-row interpretation DBCC PAGE (justdave2,1,7,3);