SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 How BACKUP/LOG history is affected by RESTORE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hillel
Starting Member

3 Posts

Posted - 09/04/2013 :  05:21:51  Show Profile  Reply with Quote
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

USA
37129 Posts

Posted - 09/04/2013 :  12:49:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Hillel
Starting Member

3 Posts

Posted - 09/05/2013 :  07:44:38  Show Profile  Reply with Quote

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


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 09/05/2013 :  12:11:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
It you run a restore, then the LSN is based upon that restore as the LSN information is stored inside the backup file. It has no concept of future LSNs.

I use RESTORE DATABASE and RESTORE LOG and never rely on backup history. I imagine that MS has this all figured out though and knows exactly what to display in that GUI screen.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Hillel
Starting Member

3 Posts

Posted - 09/05/2013 :  15:47:41  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000