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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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 Tkizer
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.
Regards
/H
|
|
|
|
|
|
|
How BACKUP/LOG history is affected by RESTORE