Restoring from SQL 2000, SQL 2005 or SQL 2008 transaction logs up to a named transaction

If you are running either SQL 2000, SQL 2005, or SQL 2008, you can restore transactions from a transaction log up to and including a named transaction (or mark). After the named transaction is reached, recovery from the transaction log is stopped.

Since named transactions do not necessarily have unique names, you can also specify a date and time after which the restore operation is to search for the named transaction. For example, if you specify a restore from a log up to the named transaction AfternoonBreak, found after 6/02/2000, 12:01 p.m., then the restore operation will not search for AfternoonBreak until after that time. To find dates and times of named transactions, check your client application event log.

If the named transaction is not found, then the restore operation succeeds, but a warning is generated and the database remains in an intermediate state.

The names of transactions are case-sensitive. Make sure you enter the correct upper- and lower-case characters when specifying a named transaction.

To restore from SQL 2000, SQL 2005, or SQL 2008 transaction logs up to a named transaction

  1. On the navigation bar, click the arrow next to Restore.

  2. Click New Restore Job.

  3. On the Properties pane, under Source, click Selections.

  4. In the restore selections list, select the most recent full database backup set, and the most recent differential database backup set, if any, and all the log backup sets you want to restore.

  5. On the Properties pane, under Settings, click Microsoft SQL.

  6. On Restore Job Properties, click Leave the database ready to use; additional transaction logs or differential backups cannot be restored.

  7. Select Restore log up to named transaction, and then enter the name of the transaction.

    The names are case-sensitive. Make sure you enter the correct upper- and lower-case characters.

  8. To include the named transaction in the restore, select Include the named transaction.

  9. To specify a particular named transaction in the log, select Found after and then select a date and time.

    If a date and time are not entered, recovery from the transaction log is stopped at the first transaction with the specified name.

  10. Start the restore job or select other restore options from the Properties pane.

More Information

Redirecting restores for SQL

Restoring data by setting job properties

About restoring SQL databases and file groups