How to restore backups with recovery even when there are subsequent backup files that need restored?



  • Suppose I have a full backup, differential backup and 10 log backup files.

    The recommended practice is to restore each with no recovery and then apply the final log backup with recovery.

    Suppose I do the prior restore with recovery, then I cannot apply the subsequent backups - it gives error.

    Is this by design and is there any way to allow for this (restore with recovery when there are subsequent backups that need restoring)? - for example when doing a database migration I want to restore the full backup and allow the target database to be used in readonly mode, and then apply differential and log backups subsequently.



  • You can set the database into https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-arguments-transact-sql?view=sql-server-ver15 while recovering, but this will be readonly.

    You cannot set it to fully recover. This is by design.

    Due to the way write-ahead logging (a system used by most RDBMSs) works, transactions may be partially written to the log at the point the backup is made. The rest of the transaction will be in the next log backup.

    Therefore, when restoring, if you tell the server to bring the database online, there will be in-flight transactions that have not yet been fully written to the main data files, but are fully present in the log files. These can be either rolled back: old data partially overwritten is copied back to the main data files; or they can be rolled forward: new data is copied into the main data files.

    Where a transaction was not fully committed to the log, there is no way to roll it forwards, as the data simply isn't there. It can only be rolled back. So to bring the database online, these transactions must be discarded.

    Then when you try restoring more log files, you will have a situation where earlier transactions have been discarded, but you are trying to restore later transactions. This will leave the database in an inconsistent state. So this is disallowed.

    Whereas if the database remains offline (or in standby), the following log backup in the sequence to be restored will have the rest of the transaction data, allowing it to be rolled forward, and later transactions can be applied over the top.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2