How to Restore a Transaction Log Backup in SQL Server

Restoring a transaction log backup in SQL Server is a crucial step in database recovery. It allows us to roll forward changes to a specific point in time. Whether you're recovering from failure, migrating data, or testing scenarios, the transaction log restore process ensures data consistency by applying committed transactions up to a specific point in time.  

In this article, we will learn the key steps, common errors, and best practices to restore transaction log backups in SQL Server successfully. 

First, let us understand the different types of backups supported in SQL Server. 

Understanding SQL Server Backup Types  

SQL Server supports five types of backups. 

Full Backup 

The full backup contains the entire database, including data and database objects, at a specific point in time. It can be used as a foundation for all other backup types, meaning that when you want to restore the differential backup and transaction log backup, a full backup is required.   

Differential Backup

The differential backup contains the data and object changes that occurred after the last full backup. The differential backups are smaller, and it takes less time to complete.  

Transaction Log Backup

The transaction log backups capture all the changes that occurred after the last full or differential backup. The transaction log backups are very small in size and usually complete very quickly. Transaction log backups are only taken when the database is in the full recovery model. 

The transaction log backups play a crucial role in the database recovery process. Here are some important notes about the transaction log backups. 

1.  Transaction log backups capture every change that happened on the database, which ensures zero data loss between full or differential backups. 
2. The transaction log backups help you to recover the database to a precise point in time, which means you can recover the database before data corruption or accidental deletion. 
3. When the database is in a full recovery model, large transactions can sometimes cause the transaction log files to become larger, leading to potential issues. When we back up a transaction log file, it truncates all the changes logged in the transaction log file, which keeps the size of the transaction log file at its original size. 

Copy-Only Backups

The copy-only backups are like a full backup, and they contain the data and structure of the database at the point in time it is taken. The only difference is that a full backup does not break the recovery chain, which is very crucial when you are using the full recovery model. If the database is in the full recovery model, and when you run a full backup, it will reset the recovery chain, which invalidates previous backups. 

File Group Backups

The file/filegroup backups are used to backup the specific data file or filegroup. These backups are useful when you want to back up the read-only file groups. 

Prerequisites for Restoring a Transaction Log Backup

Before we start restoring the transaction log backups, we must check the database server for its version, system resources, and accessible database backup file. The prerequisite checks are crucial because if any of them fail, it will interrupt the recovery process and delay it. Here is the list of items that must be checked before initiating the recovery process. 

Verify the Server Edition and Version

Always verify the server version, especially when you are restoring the backup taken on a different version or edition of SQL Server. We cannot restore the backup taken from a higher version to a lower version. For example, you cannot restore the backup of SQL Server 2022 on a SQL Server 2019 database. 

We can run the following query to check the version of SQL Server:

Select @@version 

The above query will give you the details of the SQL Server version and edition. 

Check Storage and System Resources

Next, we should check that adequate space is available after restoring full and differential backups. Sometimes we run the restore script in a sequence without verifying the available storage. In such circumstances, the restore process will be interrupted and lead to a longer downtime. Also, we must verify that the SQL Server account has the required permission to read the backup files.

Check the Backup Sequence

When we restore any database in SQL Server, we must maintain the sequence of the backups that are going to be restored. SQL Server always uses the log sequence number to track the changes in the transaction log. If you restore the backup in invalid order, it will break the LSN chain, and restoring the subsequent backups will be impossible.

Always maintain the following sequence: 

1. First, restore the full backup with the NORECOVERY option. 
2. Second, restore the differential backup, if any, with the NORECOVERY option. 
3. Lastly, restore all transaction log backups that are taken after a full or differential backup. Use the RESTORE WITH RECOVERY option. 

To check the appropriate backup files, you can use the RESTORE HEADERONLY command. The syntax is the following: 

RESTORE HEADERONLY FROM DISK = 'C:\Backups\YourBackup.bak' 

The command provides the following key information. 

1. Backup type: Shows the type of backup (full, differential, or transaction log). 
2. Position: Shows the position of the backup set within the specified backup file. 
3. First LSN and last LSN: Shows the log sequence numbers for the backup. 

You can also use the RESTORE FILELISTONLY command to check the list of database files. Here is the syntax of the command.

RESTORE FILELISTONLY FROM DISK = 'C:\Backups\YourDatabase.bak' 

The command provides the following information. 

1. Logical name: Provides the logical name of the file. 
2. Physical name: Provides the physical name of the file. 
3. Type: Shows whether the file is a log file or a data file. 

Now, let us understand how to restore a transaction log backup.

Step-by-Step Guide to Restore a Transaction Log Backup 

Now, let us understand how to restore the transaction log backups. For the demonstration, we are going to restore the stackoverflow2010 database. We will restore the transaction log backup using a T-SQL query.  

Suppose we want to restore a database on a separate server that contains all the data from the production database. The server has three backup jobs. A full backup job takes a backup every night. A differential backup job runs every 12 hours, and a transaction log backup runs every hour. 

To restore a clone on the development server, we must restore the database in the following sequence. 

1. Restore the full backup with the NORECOVERY option. 
2. Restore differential backup with the NORECOVERY option. 
3. Restore all transaction log backups taken after the last differential backup. 

We are going to restore the database using a T-SQL script. The name of the clone database will be Stackoverflow2010_Clone. First, we must restore the full backup. Here is the command.

USE [master]

Go

RESTORE DATABASE [StackOverflow2010_Clone] FROM DISK = N'D:\MS_SQL\Backup\StackOverflow2010_Full_Backup.bak'

WITH FILE = 1,

MOVE N'StackOverflow2010' TO N'D:\MS_SQL\Data\StackOverflow2010_Clone.mdf',

MOVE N'StackOverflow2010_log' TO N'D:\MS_SQL\Log\StackOverflow2010_Clone_log.ldf',

NORECOVERY, NOUNLOAD, STATS = 5

GO 

As you can see from the above screenshot, we have used the MOVE clause in the restore database. The reason is that the data file location and log file location on the clone server are different than the location of the production server; hence, we must change the physical location using the MOVE keyword. 

You can read more about the RESTORE DATABASE command here. The full backup is restored with the NORECOVERY option so that we can apply differential and log backups later. Once backup is restored, the database will be in the RESTORING state. You can run a SQL query to view the state of the database. 

select

name [Database Name],

create_date [Database create Date],

state_desc [Database State],

user_access_desc [User access status]

from sys.databases where name='StackOverflow2010_Clone' 

Query output:

Next, we will restore the differential backup. To do that, execute the following command.

RESTORE DATABASE [StackOverflow2010_Clone] FROM

DISK = N'D:\MS_SQL\Backup\StackOverflow2010_Diff_Backup.bak'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

Once differential backup is restored, we will restore the transaction log backups. To do that, we will use the RESTORE LOG command. Here is the query.

RESTORE LOG [StackOverflow2010_Clone] FROM DISK = N'D:\MS_SQL\Backup\StackOverflow2010_log_Backup_1.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

RESTORE LOG [StackOverflow2010_Clone] FROM DISK = N'D:\MS_SQL\Backup\StackOverflow2010_log_Backup_2.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

RESTORE LOG [StackOverflow2010_Clone] FROM DISK = N'D:\MS_SQL\Backup\StackOverflow2010_log_Backup_3.trn' WITH FILE = 1, NOUNLOAD, RECOVERY, STATS = 5 

Note that we restored the last log backup with the RECOVERY option. This will make a database online, and no further backup can be restored to the database. Once the database is restored, you can run the following query to check the state of the database.

select

name [Database Name],

create_date [Database create Date],

state_desc [Database State],

user_access_desc [User access status]

from sys.databases where name='StackOverflow2010_Clone'

Query output:

Error: "The log in this backup set begins at LSN..." 

Usually, this error occurs when the transaction log chain (LSN sequence) is broken. This might happen due to a missing full or differential backup or because backups are out of sequence.  

To avoid such issues from happening, before restoring the transaction log backup, always verify the backup sequence. You can use the following query to check the backup history. 

SELECT * FROM msdb.dbo.backupset

WHERE database_name = 'YourDB'

ORDER BY backup_start_date DESC; 

First, restore the most recent full backup, and then restore all subsequent differential and transaction log backups. 

Error: "The database is in use."

This error occurs when the user processes are connected to the database that is being restored.  

To rectify the error, follow the steps below:

1. Set the Database to Single-User Mode

ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Perform backup/restore

ALTER DATABASE YourDB SET MULTI_USER; 

2. Kill the Active Sessions

-- Generate kill commands for all active connections

SELECT 'KILL ' + CAST(session_id AS VARCHAR(10)) + ';'

FROM sys.dm_exec_sessions

WHERE database_id = DB_ID('YourDB');

3. Restore the Transaction Log Using the RESTRICTED_USER Option

RESTORE DATABASE YourDB FROM DISK = 'path' WITH RESTRICTED_USER; 

Conclusion 

In this article, we understand different types of backups and how to restore a transaction log backup to clone a database. We also learn about the common issues that we encounter when restoring the transaction log and how to resolve them. 

The process to repair a corrupted database by restoring native SQL backups and transaction log backups is a straightforward and simple process. But if you have a situation where an LSN chain is broken, and you do not have a compatible full backup available, then data loss is imminent. In such circumstances, we should start considering third-party tools to repair the database. Here I would like to recommend a data recovery tool, Stellar Repair for MS SQL, which provides a lot of options to recover the corrupt database.