Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hillel
Starting Member
3 Posts |
 Posted - 2013-09-04 : 05:21:51
|
| 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?Kind regards/H |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-09-04 : 12:49:18
|
| 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.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Hillel
Starting Member
3 Posts |
Posted - 2013-09-05 : 07:44:38
|
| Thank you tkizer for your reply.Just let me understand a crucial point here:"Log sequence is independent of the HISTORY"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?Regards/H |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Hillel
Starting Member
3 Posts |
Posted - 2013-09-05 : 15:47:41
|
| Thank you again TkizerYour 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.Regards/H |
|
|
|
|
|
|
How BACKUP/LOG history is affected by RESTORE