How to restore to the point of failure (Transact-SQL)

How to Install SQL Server 2000

How To

How to restore to the point of failure (Transact-SQL)

To restore to the point of failure

  1. Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back up the currently active transaction log.

  2. Execute the RESTORE DATABASE statement using the NORECOVERY clause to restore the database backup.

  3. Execute the RESTORE LOG statement using the NORECOVERY clause to apply each transaction log backup.

  4. Execute the RESTORE LOG statement using the RECOVERY clause to apply the transaction log backup created in Step 1.
Examples

This example backs up the currently active transaction log of the MyNwind database, even though MyNwind is inaccessible, and then restores the database to the point of failure using previously created backups:

-- Back up the currently active transaction log.
BACKUP LOG MyNwind
   TO MyNwind_log2
   WITH NO_TRUNCATE
GO
-- Restore the database backup.
RESTORE DATABASE MyNwind
   FROM MyNwind_1
   WITH NORECOVERY
GO
-- Restore the first transaction log backup.
RESTORE LOG MyNwind
   FROM MyNwind_log1
   WITH NORECOVERY
GO
-- Restore the final transaction log backup.
RESTORE LOG MyNwind
   FROM MyNwind_log2
   WITH RECOVERY
GO

See Also

RESTORE

Restoring a Database to a Prior State