When you restore a full database backup, you get a transactionally consistent database. Consider the case where there’s an active transaction that doesn’t commit until after the backup completes. If the backup only contained the log that occured while the database was being backed up, how would it roll back the active transaction. It *has* to include enough transaction log to roll back the active transaction. The start LSN of the log included in a database backup is the minimum of:

• LSN of the last checkpoint
  
• LSN of the start of the oldest active transaction
  
• LSN of the last replicated transaction

Let me prove it to you. I’m going to create a database, put it into FULL recovery mode, start a transaction, checkpoint, and then take a backup. The checkpoint ensures the page I’ve altered is flushed to disk.

CREATE DATABASE stopattest;

GO

ALTER DATABASE stopattest SET RECOVERY FULL;

GO

BACKUP DATABASE stopattest TO DISK = ‘c:\sqlskills\stopattest.bck’ WITH INIT;

GO

USE stopattest;

GO

CREATE TABLE t1 (c1 INT);

GO

BEGIN TRAN;

INSERT INTO t1 VALUES (1);

GO

Now in another connection I’ll take another full database backup.

BACKUP DATABASE stopattest TO DISK = ‘c:\sqlskills\stopattest.bck’ WITH INIT;

GO

The msdb.dbo.backupmedia table will tell us the relevant LSNs in the backup (I added some spaces to delineate the prts of the LSN for clarity):

SELECT last_lsn, checkpoint_lsn, database_backup_lsn FROM msdb.dbo.backupset

WHERE database_name = ‘stopattest’;

GO

last_lsn              checkpoint_lsn        database_backup_lsn
——————— ——————— ———————
21 0000000190 00001   21 0000000174 00037    21 0000000058 00037

[Edit: After swapping some email with Kalen Delaney, I realized that when I originally put this together I had more log records in the post and when I removed them I messed up the description of the (21:174:37) LSN - its now corrected below)

So you can see the checkpoint that begins the backup was at (21:174:37). The LSN of the first log record that the backup contains is (21:58:37), which is before the start of the backup. And the backup contains all the log from then until (21:190:1). Now let's look at the actual transaction log to see what these LSNs correspond to.

SELECT [Current LSN], Operation, [Transaction Name] FROM fn_dblog (null,null);

GO

Here’s some selected output:

Current LSN              Operation        Transaction Name
———————— —————- ——————
00000015:0000003a:0025   LOP_BEGIN_CKPT   NULL
                (this is the calculated minimum LSN the backup must contain (21:58:37) – which is (15:3a:25) in hex)
.
.
00000015:00000061:0001   LOP_BEGIN_XACT   user_transaction
                (here’s my transaction starting – before the backup started but within the LSN range contained in the backup)
.
.
00000015:000000ab:0004   LOP_BEGIN_XACT   Backup:InvalidateDiffMaps
                (this is the backup clearing the differential bitmaps)
.
.
00000015:000000ae:0025  LOP_BEGIN_CKPT    NULL
                (this is the checkpoint that BACKUP does – matching the checkpoint LSN above)
.
.

So – this clearly shows that the backup contains more than just the log from the time the backup was running.

Myth 2: It’s possible to do a STOPAT with only a full database backup

This myth is that its possible to use STOPAT with a full database backup to stop during the time the backup was being taken. The argument FOR this myth is that the backup contains the log for all the changes that happened while the backup was being taken, so it must be possible to stop at any point in time. Technically, that’s correct, but in practice it’s wrong – you cannot stop at a point while the backup was running, using only the database backup.

This one’s more complicated to explain. Doing a STOPAT operation means getting the database to a state where operations later than the time or LSN specified in the STOPAT clause haven’t affected the database yet. A database backup reads pages that may or may not have been changed while the backup was running. If they are changed, it could be at any point while the backup is running.

Consider the case where page X is changed at LSN (10:45:12), *just* before the backup completes and is read by the backup at the time equivalent to LSN (10:45:13). The backup will contain the changed page image, plus the log record for the change. What if I want to stop at a point while the backup was running but *before* the change to page X, say at LSN (10:44:00). The backup only contains the image of page X at LSN (10:45:12) - how can it be put back to the image at the time we want to stop at? The argument is that we have the log record for the change – can’t SQL Server just undo it?

No. It won’t even see it. STOPAT works by recovering the database up to the point that the STOPAT specified. If we ask to stop at LSN (10:44:00), then the log will only be read and recovered up to that point. However, because the database backup didn’t read page X until LSN (10:45:13), it only has the image of it from when it was altered at (10:45:12). This clearly won’t give a database image as of (10:44:00).

The only way to stop at a particular time/LSN, is to have images of *all* database pages from before that time/LSN (i.e. from the *previous* database backup) and then restore all the transaction logs up to and including the time/LSN to stop at.

Hopefully that makes sense.