How BACKUP/LOG history is affected by RESTORE-ing a DATABASE to a past point in time?
I have faced the following scenario:
• A database maintains a regular routine of periodical BACKUP/LOG-s discipline. • Therefore - a set of associated entries is maintained at BACKUP history bookkeeping tables with appropriate LSN sequencing. • Now – one decided to RESTORE (retract) the database to a past point in time – say – a month ago. • My question is how this affects the association between current database 'timed state' and the BACKUP/LOG history contents? • We have a database that will sequence LSN starting at – say 1000 – while HISTORY can indicate entries having LSN-s 2000, 3000 etc. • Next BACKUP/LOG will record history entry with LSN 1000 - so LOG sequence is broken. • Future possible RESTORE that would wish to take BACKUP/LOG-s and replay them forward can't decide who is who. • Is there any automatic way to control disordered situations of that kind?
Log sequence is independent of the HISTORY, so you the LOG sequence is NOT broken.
I don't use HISTORY to do my restores. I just restore via RESTORE DATABASE and RESTORE LOG commands. My files are named in such a way that they are ordered alphabetically by date, so I just restore in sequence according to the file system when sorted alphabetically. I even have a handy script to help me generate the commands if I have a lot of them to restore.
You should have a job in place to trim the backup history anyway.
My special interest is in the LSN sequencing along HISTORY.
I have a database which is intact. LSN-s increment ordinarily and BACKUP/LOG maintain that LSN-s sequencing. So – everything is OK.
Now one comes and retracts the database version one month backwards. While doing that - BACKUP/LOG history remains untouched!!
Next – database activity resumes to normal functionality, based on that 'past' database contents.
My specific question is: What is the base LSN sequence upon resuming activity? Does it proceed from the most recent 'present' LSN, regardless of reverting to a past point in time? Or – does it proceed from the highest LSN that prevailed 1 month ago?
If the latter case is true – how can LSN sequence continuity be preserved at HISTORY? Next BACKUP/LOG will certainly create overlapping LSN-s ranges at HISTORY records. Is it correct? In short - how does LSN sequencing work in this case?
Your answer matches my empirical experience. I just wanted to approve that I do not miss something. So - next LSN is based on the RESTORE-d information as held inside the BACKUP. One should be aware of that when relying on LSN-s with respect to HISTORY.