Muokkaa

Restore a transaction log backup (SQL Server)

Applies to: SQL Server

You can restore a transaction log backup in SQL Server by using SQL Server Management Studio, Transact-SQL, or PowerShell.

Prerequisites

You must restore backups in the order you created them. Before you restore a specific transaction log backup, restore the following earlier backups without rolling back uncommitted transactions, by using WITH NORECOVERY:

  • The full database backup and, optionally, the last differential backup taken before that transaction log backup. The database must have used the full or bulk-logged recovery model before the most recent full or differential database backup was created.

  • All transaction log backups taken after the full database backup or the differential backup (if you restore one) and before that transaction log backup. You must apply log backups in the order you created them, without any gaps in the log chain.

For more information about transaction log backups, see Transaction log backups and Apply Transaction Log Backups.

Permissions

For an existing database, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.

RESTORE permissions go to roles whose membership the server can always check. The server can check fixed database role membership only when the database is accessible and undamaged, which isn't always true when RESTORE runs. For this reason, members of the db_owner fixed database role don't have RESTORE permissions.

Remarks

For a mirror database, or an availability group secondary database that you haven't joined to the availability group, restore the last log backup with the NORECOVERY option (-NoRecovery in PowerShell). The database must remain in the restoring state.

For a log shipping secondary database, restore the last log backup with the NORECOVERY option or the STANDBY option (-StandbyFile in PowerShell). The STANDBY option gives read-only access to the database between restores.

Use SQL Server Management Studio

Warning

In a typical restore, you select the log backups together with the data and differential backups in the Restore Database dialog box.

  1. Connect to the appropriate instance of the SQL Server Database Engine. In Object Explorer, select the server name to expand the server tree.

  2. Expand Databases. Select a user database, or expand System Databases and select a system database.

  3. Right-click the database, point to Tasks, point to Restore, and then select Transaction Log. The Restore Transaction Log dialog box opens.

    Note

    If Transaction Log is unavailable, you might need to restore a full or differential backup first. Use the Restore Database dialog box.

  4. On the General page, in the Database list box, select the database. The list shows only databases in the restoring state.

  5. To specify the source and location of the backup sets to restore, select one of the following options:

    • From previous backups of database

      Select the database to restore from the dropdown list. The list contains only databases with backups recorded in the msdb backup history.

    • From file or tape

      Select the browse (...) button to open the Select backup devices dialog box. In the Backup media type box, select a device type. To add one or more devices to the Backup media box, select Add.

      After you add the devices you want, select OK to return to the General page.

  6. In the Select the transaction log backups to restore grid, select the backups to restore. The grid lists the transaction log backups available for the selected database. The grid lists log backups in the order of the log sequence numbers (LSNs) they contain, and you must restore them in this order.

    The following table lists the column headers of the grid and describes their values.

    Header Value
    Restore Selected checkboxes indicate the backup sets to restore.
    Name Name of the backup set.
    Component Backed-up component: Database, File, or <blank> (for transaction logs).
    Database Name of the database involved in the backup operation.
    Start Date Date and time when the backup operation began, presented in the regional setting of the client.
    Finish Date Date and time when the backup operation finished, presented in the regional setting of the client.
    First LSN Log sequence number of the first transaction in the backup set. Blank for file backups.
    Last LSN Log sequence number of the last transaction in the backup set. Blank for file backups.
    Checkpoint LSN Log sequence number of the most recent checkpoint at the time the backup was created.
    Full LSN Log sequence number of the most recent full database backup.
    Server Name of the Database Engine instance that performed the backup operation.
    User Name Name of the user who performed the backup operation.
    Size Size of the backup set in bytes.
    Position Position of the backup set in the volume.
    Expiration Date and time the backup set expires.

  7. Select one of the following options:

    • Point in time

      Keep the default (Most recent possible), or select the browse button to open the Point in Time Restore dialog box and select a specific date and time.

    • Marked transaction

      Restore the database to a previously marked transaction. This option opens the Select Marked Transaction dialog box. The grid in this dialog box lists the marked transactions in the selected transaction log backups.

      By default, the restore operation goes up to, but excludes, the marked transaction. To include the marked transaction in the restore, select Include marked transaction.

      The following table lists the column headers of the grid and describes their values.

      Header Value
      <blank> Displays a checkbox for selecting the mark.
      Transaction Mark Name of the marked transaction specified by the user when the transaction was committed.
      Date Date and time of the transaction when it was committed. Transaction date and time are displayed as recorded in the logmarkhistory table in msdb, not in the client computer's date and time.
      Description Description of the marked transaction, if any, specified by the user when the transaction was committed.
      LSN Log sequence number of the marked transaction.
      Database Name of the database where the marked transaction was committed.
      User Name Name of the database user who committed the marked transaction.

  8. To view or select the advanced options, select Options in the Select a page pane.

  9. In the Restore options section, select from the following options:

    • Preserve the replication settings (WITH KEEP_REPLICATION)

      Preserves the replication settings when you restore a published database to a different server from the one where the database was created.

      This option is available only with the Leave the database ready for use by rolling back uncommitted transactions... option (described later), which is equivalent to restoring a backup with the RECOVERY option.

      Selecting this option is equivalent to using the KEEP_REPLICATION option in a Transact-SQL RESTORE statement.

    • Prompt before restoring each backup

      Before each backup set after the first, this option opens the Continue with Restore dialog box, which asks whether you want to continue the restore sequence. The dialog box shows the name of the next media set (if available), the backup set name, and the backup set description.

      This option is especially useful when you must swap tapes for different media sets, for example, when the server has only one tape device. Select OK only when you're ready to continue.

      If you select No, the database stays in the restoring state. You can continue the restore sequence later, from the last restore that completed. If the next backup is a data or differential backup, use the Restore Database task again. If the next backup is a log backup, use the Restore Transaction Log task.

    • Restrict access to the restored database (WITH RESTRICTED_USER)

      Makes the restored database available only to the members of db_owner, dbcreator, or sysadmin.

      Selecting this option is equivalent to using the RESTRICTED_USER option in a Transact-SQL RESTORE statement.

  10. Under Recovery state, specify the state of the database after the restore operation.

    • Leave the database ready for use by rolling back uncommitted transactions. Additional transaction logs can't be restored. (RESTORE WITH RECOVERY)

      Recovers the database. This option is equivalent to the RECOVERY option in a Transact-SQL RESTORE statement.

      Choose this option only if you have no other log backups to restore.

    • Leave the database non-operational, and don't roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)

      Leaves the database unrecovered, in the RESTORING state. This option is equivalent to using the NORECOVERY option in a Transact-SQL RESTORE statement.

      When you choose this option, the Preserve the replication settings option is unavailable.

    • Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a file so that recovery effects can be reversed. (RESTORE WITH STANDBY)

      Leaves the database in a standby state. This option is equivalent to using the STANDBY option in a Transact-SQL RESTORE statement.

      This option requires a standby file.

  11. If you leave the database in read-only mode, specify a standby file name in the Standby file text box. You can browse for the standby file or type its path in the text box.

Use Transact-SQL

Important

To avoid ambiguity, explicitly specify either WITH NORECOVERY or WITH RECOVERY in every RESTORE statement, especially in scripts.

  1. For each transaction log backup before the last one in your restore sequence, run a RESTORE LOG statement with the following values:

    • The name of the database to apply the transaction log to.
    • The backup device that contains the transaction log backup.
    • The NORECOVERY clause.

    The following example shows the basic syntax, where <database_name> is the name of the database and <backup_device> is the name of the device that contains the log backup.

    RESTORE LOG <database_name>
FROM <backup_device>
WITH NORECOVERY
[ ; ]

    If your restore sequence has only one log backup, skip this step.

  2. For the last log backup in your restore sequence, use one of the following methods to recover the database:

    • Recover the database as part of the last RESTORE LOG statement:

      RESTORE LOG <database_name>
FROM <backup_device>
WITH RECOVERY;
GO

    • Recover the database later, with a separate RESTORE DATABASE statement:

      RESTORE LOG <database_name>
FROM <backup_device>
WITH NORECOVERY;

RESTORE DATABASE <database_name>
WITH RECOVERY;
GO

      The database stays in the restoring state after the last RESTORE LOG statement. You can check that you restored every necessary log backup, and then recover the database. This approach is often useful for a point-in-time restore.

Examples (Transact-SQL)

By default, the AdventureWorks2025 database uses the simple recovery model. The following examples require backups taken under the full recovery model. Before you create those backups, run the following statement to change the recovery model:

ALTER DATABASE AdventureWorks2025
SET RECOVERY FULL;

The switch to the full recovery model takes effect only after the first data backup. Take a full database backup to start the log chain.

The following examples restore the backups to an instance where the AdventureWorks2025 database doesn't exist, such as a different server.

A. Apply a single transaction log backup

The following example restores the AdventureWorks2025 database from a full database backup on a backup device named AdventureWorks2025_1. It then applies the first transaction log backup from a backup device named AdventureWorks2025_log. Finally, it recovers the database.

RESTORE DATABASE AdventureWorks2025
FROM AdventureWorks2025_1
WITH NORECOVERY;
GO

RESTORE LOG AdventureWorks2025
FROM AdventureWorks2025_log
WITH FILE = 1,
     NORECOVERY;
GO

RESTORE DATABASE AdventureWorks2025
WITH RECOVERY;
GO

B. Apply multiple transaction log backups

The following example restores the AdventureWorks2025 database from a full database backup on a backup device named AdventureWorks2025_1. It then applies, one by one, the first three transaction log backups from a backup device named AdventureWorks2025_log. Finally, it recovers the database.

RESTORE DATABASE AdventureWorks2025
FROM AdventureWorks2025_1
WITH NORECOVERY;
GO

RESTORE LOG AdventureWorks2025
FROM AdventureWorks2025_log
WITH FILE = 1,
     NORECOVERY;
GO

RESTORE LOG AdventureWorks2025
FROM AdventureWorks2025_log
WITH FILE = 2,
     NORECOVERY;
GO

RESTORE LOG AdventureWorks2025
FROM AdventureWorks2025_log
WITH FILE = 3,
     NORECOVERY;
GO

RESTORE DATABASE AdventureWorks2025
WITH RECOVERY;
GO

Use PowerShell

Use the Restore-SqlDatabase cmdlet, and set the -RestoreAction parameter to Log. Include the -NoRecovery parameter for every restore in the sequence except the last one. Omit -NoRecovery on the last log restore to recover the database.

Note

These examples require the SqlServer module. For more information, see SQL Server PowerShell Provider.

For the full syntax, see Restore-SqlDatabase.

Examples (PowerShell)

The following examples restore the backups to an instance where the AdventureWorks2025 database doesn't exist, such as a different server.

A. Apply a single transaction log backup

The following example restores the AdventureWorks2025 database from a full database backup, leaving the database in the restoring state. It then applies a transaction log backup and recovers the database.

$instance = "Computer\Instance"

$fullRestore = @{
    ServerInstance = $instance
    Database       = "AdventureWorks2025"
    BackupFile     = "C:\Backup\AdventureWorks2025_full.bak"
    RestoreAction  = "Database"
    NoRecovery     = $true
}
Restore-SqlDatabase @fullRestore

$logRestore = @{
    ServerInstance = $instance
    Database       = "AdventureWorks2025"
    BackupFile     = "C:\Backup\AdventureWorks2025_log1.trn"
    RestoreAction  = "Log"
}
Restore-SqlDatabase @logRestore

B. Apply multiple transaction log backups

The following example restores the full database backup, and then applies three transaction log backups in sequence. Every restore except the last one uses -NoRecovery, so the last log restore recovers the database.

$instance = "Computer\Instance"
$logBackups = @(
    "C:\Backup\AdventureWorks2025_log1.trn",
    "C:\Backup\AdventureWorks2025_log2.trn",
    "C:\Backup\AdventureWorks2025_log3.trn"
)

$fullRestore = @{
    ServerInstance = $instance
    Database       = "AdventureWorks2025"
    BackupFile     = "C:\Backup\AdventureWorks2025_full.bak"
    RestoreAction  = "Database"
    NoRecovery     = $true
}
Restore-SqlDatabase @fullRestore

for ($i = 0; $i -lt $logBackups.Count; $i++) {
    $isLast = ($i -eq $logBackups.Count - 1)
    $logRestore = @{
        ServerInstance = $instance
        Database       = "AdventureWorks2025"
        BackupFile     = $logBackups[$i]
        RestoreAction  = "Log"
        NoRecovery     = -not $isLast
    }
    Restore-SqlDatabase @logRestore
}

Related content

  • Viimeksi päivitetty