Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    144

    Exclamation Unanswered: Transanction Log Backups

    i have a database backup and about 10 transanction log backups. How do i apply the transanction log backups ?

    I have restored the full backup. Do i apply the latest transanction log or do i have to apply them in order ?

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You have to use WITH NORECOVERY on the original restore from the full backup file. From Books Online:

    NORECOVERY

    Instructs the restore operation to not roll back any uncommitted transactions. Either the NORECOVERY or STANDBY option must be specified if another transaction log has to be applied. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.

    SQL Server requires that the WITH NORECOVERY option be used on all but the final RESTORE statement when restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed (for example, a full database backup followed by a differential database backup).



    Note When specifying the NORECOVERY option, the database is not usable in this intermediate, nonrecovered state.


    When used with a file or filegroup restore operation, NORECOVERY forces the database to remain in the restoring state after the restore operation. This is useful in either of these situations:

    A restore script is being run and the log is always being applied.


    A sequence of file restores is used and the database is not intended to be usable between two of the restore operations.
    You then restore the log backups. From Books Online again:

    B. Applying multiple transaction log backups
    This example applies multiple transaction log backups to the MyNwind database.

    RESTORE LOG MyNwind
    FROM MyNwind_log1
    WITH NORECOVERY
    GO
    RESTORE LOG MyNwind
    FROM MyNwind_log2
    WITH NORECOVERY
    GO
    RESTORE LOG MyNwind
    FROM MyNwind_log3
    WITH RECOVERY
    GO
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •