Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
38200 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
38200 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  
 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.02 seconds. Powered By: Snitz Forums 2000