This is another excellent question. This question actually covers the myth of a full database backup only contains the transaction log from the start of the backup to the end of the backup.
Backupset table in msdb database has many columns that contain the details of the database backup. Following are the columns that are of interest for this quiz.
First_LSN: It is the log sequence number of the first or oldest log record in the backup set. . If database is idle FirstLSN and CheckpointLSN will be the same. It contains the LSN of an open transaction.
LastLSN: It is the log sequence number of the next log record after the backup set. This value is greater than FirstLSN and Checkpoint_LSN.
CheckpointLSN: It is the log sequence of the log record where the redo must start. It will contain the LSN of a backup check point, and this value will be greater than FirstLSN.
Databasebackuplsn: It is the log sequence number of the most recent full database backup. When we compare new and previous full database backup we can see that DatabaseBackupLSN of the new backup is same as Checkpoint_LSN of the old full database backup.
An open transaction which is issued before a backup operation has no effect on the DatabaseBackupLSN and Last_LSN. These values will be the same if a transaction is issued before or after a backup operation start.
Basically, when we restore a full database backup, we get a transitionally consistent database. In the case where there is an active transaction that does not commit until after the backup completes, if the backup only contained the log occurred while the database is being backed up, how would it roll back the active transaction. This includes enough transaction logs to roll back the active transaction.
The start of the LSN of the log included in a database backup is the minimum of the following items:
- LSN of the last checkpoint
- LSN of the start of the oldest
active transaction
- LSN of the last replicated
transaction
Let us demonstrate this with an example.
- Create a database SQLDBAQUIZ2011
- Place the database into Full Reocovery mode.
- Start a transaction without a commit.
- Checkpoint
- Take a backup of the SQLQUIZ2011.
The checkpoint command makes sure that the paget that is going to be altered will be flushed to the disk and gets hardened there.
CREATE DATABASE SQLQUIZ2011;
GO
ALTER DATABASE SQLQUIZ2011 SET RECOVERY FULL;
GO
BACKUP DATABASE SQLQUIZ2011
TO DISK = 'D:\SQLBackups\sqlquiz2011db.bak' WITH INIT;
GO
USE SQLQUIZ2011;
GO
CREATE TABLE Quiz27 ( quiz_id INT);
GO
BEGIN TRAN;
INSERT INTO Quiz27 VALUES (1);
GO
Now in another Query analyzer with a new connection, let us take a full database backup.
BACKUP DATABASE SQLQUIZ2011
TO DISK = 'D:\SQLBackups\sqlquiz2011db.bak' WITH INIT;
GO
The msdb.dbo.backupset table from the msdb database will tell us the relevant LSNs in the backup as shown below:
SELECT first_lsn, last_lsn, checkpoint_lsn, database_backup_lsn
FROM msdb.dbo.backupset
WHERE database_name = 'SQLQUIZ2011';
GO
We can see from the figure shown below that the checkpoint that begins the backup was at (37:101:148). The log sequence number (LSN) of the first log record the backup contains is (37:55:64) and this is before the start of the SQLQUIZ2011 database backup. So the backup contains the entire log from then until (37:162:01).
Result of the Above Query: Figure 1
Now let us analyze and look at the actual transaction log records to see what the LSNs correspond to using the following query:
SELECT [Current LSN], Operation, [Transaction Name]
FROM fn_dblog (null,null);
GO
From the query we get the current LSN, operation details and the transaction name. Since the output is huge, and I am not adding here,if you want to test, you can use the above demo to see the output.
Output of the above query shows that the backup contains more than just the log from the time the backup was running.
So, there will be no impact of an open transaction for backup. The transaction will be rolled back if the restore sequence ends with restoring that backup.