1

If I have this backup strategy(weekly full backup and 1 hour log backup), can I restore the database to the green highlighted period of time? Btw, does log backup 2 includes all log records that have their lsn great than the last_lsn of log backup 1?

enter image description here


I did some more test and I think I found the answer. According to the image in the backup schedule, I did an initial full backup, some log backups, then log backup 1, a full backup, log backup 2. I have modifications in between. Use RESTORE HEADERONLY to check log backup 1, full backup and log backup 2, below is what I got. As you can see, log backup 2 catches all log records from the last one in log backup 1. If I want to restore to a point in the green highlighted period, I need to use log backup 2, not the full back.

enter image description here

6

Yes, you could do a point-in-time restore.

You would need the last full backup and all log backups up to the point you need, as log backups do not contain all earlier logs (they are in one sense incremental, rather than differential).

For a week's worth of logs on a busy database, it may take some time to restore. Therefore a better solution (if you want to stick to weekly fulls) is to do a differential backup every day or twice a day.

This means that you only need the last full backup, the last diff backup, and all logs going forward up to whenever you want to restore.


A fairly standard backup cycle on many servers is: full daily, diff 4-6 per day and logs every few minutes. This gives a pretty fast restore time. It also means more importantly, that your maximum data loss is never more than a few minutes, rather than up to an hour.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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