All we need is an easy explanation of the problem, so here it is.
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?
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂