1

Our client will ship a database full dump on day 1 and daily transaction log from day 2. We will restore the full dump on day 1 and plan to restore the transaction log daily since day 2. Is it possible to just restore the daily transaction log? Because I read in order to restore a transaction log backup, the database has to be in a restoring state which means that a full backup or a full backup and differential backup were first restored using the NORECOVERY option to allow additional backups. Does it mean on everyday in order to restore the daily transaction log we have to first restore the full dump and leave the db in NORECOVERY mode and restore all the transaction logs before current day and leave the db in NORECOVERY mode?

Improve this question
3
  • 2
    If the restored database can be readonly, you might be able to use RESTORE WITH STANDBY. That option allows you to restore the full and diff with norecovery and the transaction log restores would be with standby. You can read more about STANDBY on the RESTORE DATABASE page.
    – Scott Hodgin - Retired
    Jan 3, 2019 at 13:37
  • I have read about the standby but not sure what it is. Does it allow daily restore from transaction log without first do the full restore?
    – thotwielder
    Jan 3, 2019 at 14:41
  • standby allows you to apply successive transation log backup without having to start back at the full/diff. The restored database is readonly and cannot be in an upgrade situation where the restore instance is at a later SQL Server version than the source instance. I'm sure there are plenty of examples on the Internet about using standby
    – Scott Hodgin - Retired
    Jan 3, 2019 at 14:45

1 Answer 1

Reset to default
3

Is it possible to just restore the daily transaction log?

As you already pointed out, restoring just the transaction log is NOT possible you have to either restore full backup or full and differential backup to restore transaction log backups.

Does it mean on everyday in order to restore the daily transaction log we have to first restore the full dump and leave the db in NORECOVERY mode and restore all the transaction logs before current day and leave the db in NORECOVERY mode?

Yes. Are you aware about transaction log shipping this might suit your requirement.

Improve this answer
1
  • Yes, TRANSACTION LOG SHIPPING is the solution that allows to leave the copy DB in read-only but allows to configure to restore the LOGS incrementally. Voted up
    – Angel M.
    Jan 3, 2019 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.