Transaction log is exclusively identified by log sequence number (LSN) in SQL Server. Whenever the users take, the backup of transaction log then, it is stored in LSN form. These are used as internally during a RESTORE sequence to track the path of data, which has been restored. When the backup is restored then, the data is restored to corresponding LSN to the point at which the backup was taken. The LSN of log record on which a given backup and restore events are occurred is view-able by using the discussed command in the following section.
While restoring the database, the RESTORE sequence of the initial database begins from FULL database backup. It does not begin with transactional log and differential file backup. There are four important Log Sequence Numbers while restoring the database as mentioned:
User can retrieve these values from backup file of SQL Server by using RESTORE HEADERONLY command. Some attributes of various database backups are discussed below.
Full database backup LSNs
Differential database backup LSNs
Transactional log backups LSNs
A new log chain starts with full database backup by following the database creation or after switching from simple recovery model to bulk or full-logged recovery model. The first log backup in a chain is backupset.begins_log_chain = 1.
The first_lsn and last_lsn are used to link the backups of log into consecutive sequence of log chain. User can use the sequence of consecutive log backups to roll forward a database from the differential backup, recent data, damaged data or earlier backup of past missing data. The first_lsn is the LSN of the first log record in the log backup and beginning with this, it includes log records up, but excluding the records of log, whose LSN is last_lsn. There are two consecutive backups log if last record of earlier backup, i.e. Backup_C is greater than or equal to the first log record, i.e. Backup_D. This will be more understandable as Backup_C.last_lsn >= Backup_D.first_lsn. If this condition is false then, there will be a gap between two backups. The importance of relationship between these Log Sequence Numbers is as followed:
If C.last_lsn = D.first_lsn then, D is the log backup that is taken directly after C.
If C.lst_lsn > D.first_lsn, then, overlap exists.
Overlap results in creating a copy-only log backup or the first log backup that is taken after a point in recovery time. It can involve various recovery forks.
Log sequence number for backup is important to understand while working with SQL Server. As it helps to restore the data from a disaster situation by using various commands, out of which one command is discussed above. This command helps to restore the headers part from various database backups. Along, with this, concept of log sequence numbers and backups are discussed.