Restore a transaction log backup

Restoring a transaction log backup recovers a database to a specific point in time by applying recorded transactions from one or more transaction log backups. Unlike a full or differential backups, which contain data pages, a transaction log backup includes all changes made since the previous log backup.

If you have multiple transaction log backups to restore, you must restore them sequentially in the order they were created with the NORECOVERY option and use the RECOVERY option only on the final restore to bring the database online.

Prerequisites

• Connect to the required SQL Server instance.

• Create a full backup and one or more transaction log backups of the database. If a differential backup exists between them, it must also be included in the restore sequence. For more information, see Back up a database.

Important

The created backup files with a .bak or .trn extension must be located on the server where the SQL Server instance is installed. If the backup files are stored on your local machine, copy them to the server before restoring the database.

• Restore the full (and if applicable, differential) backup using the NORECOVERY option. For more information, see Restore a full backup and Restore a differential backup.

To restore a transaction log backup

1. In Database Explorer, right-click the database you want to restore, and select Tasks > Restore.

2. On the Source and Target page, under Source for restore, specify the source of the backup to restore:

• Database: Select the database from the list to restore from the SQL Server backup history. Use this option if the backup was created on the same SQL Server instance — SQL Server will use backup metadata from the msdb system database to locate the available backups.

• Files: Specify the path to a .bak or .trn file stored on the SQL Server machine and the name of the database that the backup file contains. Use this option to restore directly from a backup file, for example, if the file was copied to the server and is not included in the backup history.

3. Under Destination for restore, in Database, select the database that you want to restore the transaction log backup to.

4. Optional: For a point-in-time restore, in Restore to, specify the point in time to which the database needs to be restored:

• To restore the database to the most recent available state, keep the default To the last backup taken option.

• To restore the database to a specific point in time, select the desired date and time in the picker.

• To view all backups required to restore the database as close as possible to the current time in the Backup set to restore grid, click Now in the Restore to date and time picker, and then click OK.

The list of backups in the Backup set to restore grid automatically updates based on the selected date and time.

Note:

Restore to is available only when backup metadata is accessible—for example, when restoring from backup history or from backup files in the SQL Server default backup directory. If this option is unavailable, the database is restored to the end of the selected backup.

5. In the Backup set to restore grid, review the available backups and select the backup you want to restore:

When you select a transaction log backup in the grid, the full backup, the differential backup (if applicable), and all preceding transaction log backups are automatically selected.

Important

If you have multiple transaction log backups to restore, you must restore them one at a time in the order they were created.

6. On the Files page, verify that the file locations match those used in previous restore operations (full and differential backups).

Note

You must use the same file locations as in the previous restore operations. The transaction log backup applies logged changes to the existing database files restored from the full or differential backup.

You can change the default file locations in one of the following ways:

• To relocate all files to specific folders, select Relocate database files to folder, then specify Data file folder for the data file and Log file folder for the log file.

• To specify a new location for each file, in the Restore database files as grid, modify the path in the Restore As column for each file.

7. On the Options page, in Recovery state, select the recovery state for the restored database:

• RESTORE WITH RECOVERY – The database is fully restored and ready for use immediately. Uncommitted transactions are rolled back, and no additional backups can be applied. Use this option for the final backup in your restore sequence when you want to bring the database online.

• RESTORE WITH NORECOVERY – The database remains non-operational, and uncommitted transactions are not rolled back. Additional transaction log backups can be applied. Use this option for all transaction log backups except the final one.

• RESTORE WITH STANDBY – The database is left in read-only mode, allowing queries while additional restores are pending. Uncommitted transactions are undone and saved in a standby file, allowing recovery effects to be reverted. Requires specifying a Standby file path.

8. Optional: To prevent restore failures caused by active connections, select Close existing connections to destination database to close all connections to the target database.

9. Click Restore.

After the transaction log backup is restored successfully, you can continue restoring any remaining transaction log backups with the NORECOVERY option until you restore the final one with the RECOVERY option, which brings the database back online.

Note

You can also use dbForge Transaction Log for SQL Server to partially or fully restore lost data by reading transaction log files. For more information, see Read transaction logs.